Page 1 of 1

How to Close open documents?

Posted: Thu Apr 19, 2012 10:56 pm
by tech_soul8
Hello!

I wrote this script:

Dim oExcel, Workbook, File, oFSO

Set ofso = CreateObject ("Scripting.FileSystemObject")
Set file = oFSO.GetFile ("C:Documents and SettingsAdministratorDesktopBook1.xlsx")

Set oExcel = CreateObject ("Excel.Application")
oExcel.Visible = True
Set Workbook = oExcel.Workbooks
Workbook.Open (file)
Workbook.Close
oExcel.Quit

and it works fine.

Now I'm interested about how can I close already open documents (workbooks) with script?

For example: When I open above document (Book1.xlsx) from within a script everything works fine (all the code bellow). But if I open a file "Book1.xlsx" manually and omit reference to file within my script so it looks like this:

Dim oExcel, Workbook

Set oExcel = CreateObject ("Excel.Application")
oExcel.Visible = True
Set Workbook = oExcel.Workbooks
Workbook.Close
oExcel.Quit

my Book1.xlsx file is still open after running a script.

I suppose that I need to loop somehow through manually opened files and set reference to them so script can close them all. What I'm missing in the second code?

Thanks

How to Close open documents?

Posted: Thu Apr 19, 2012 10:56 pm
by tech_soul8
Hello!

I wrote this script:

Dim oExcel, Workbook, File, oFSO

Set ofso = CreateObject ("Scripting.FileSystemObject")
Set file = oFSO.GetFile ("C:Documents and SettingsAdministratorDesktopBook1.xlsx")

Set oExcel = CreateObject ("Excel.Application")
oExcel.Visible = True
Set Workbook = oExcel.Workbooks
Workbook.Open (file)
Workbook.Close
oExcel.Quit

and it works fine.

Now I'm interested about how can I close already open documents (workbooks) with script?

For example: When I open above document (Book1.xlsx) from within a script everything works fine (all the code bellow). But if I open a file "Book1.xlsx" manually and omit reference to file within my script so it looks like this:

Dim oExcel, Workbook

Set oExcel = CreateObject ("Excel.Application")
oExcel.Visible = True
Set Workbook = oExcel.Workbooks
Workbook.Close
oExcel.Quit

my Book1.xlsx file is still open after running a script.

I suppose that I need to loop somehow through manually opened files and set reference to them so script can close them all. What I'm missing in the second code?

Thanks

How to Close open documents?

Posted: Thu Apr 19, 2012 11:45 pm
by jvierra
That is correct. You cannot close what you did not open.

You can attach to the open Excel session with GetObject.

Set xl = GetObject(,"Excel.Application")xl.quit

How to Close open documents?

Posted: Fri Apr 20, 2012 12:49 am
by tech_soul8
I figured it out :)) I created new variable and set reference to xl :) like this:

Set xy = xl.workbooks

and now I can access to methods,properties...just like I created new object with CreateObject function and I can save documents before exiting appliaction.

Book question is still opened :)