How to Close open documents?

Anything VBScript-related, including Windows Script Host, WMI, ADSI, and more.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
User avatar
tech_soul8
Posts: 26
Joined: Mon Apr 02, 2012 8:46 pm

How to Close open documents?

Post 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

User avatar
tech_soul8
Posts: 26
Joined: Mon Apr 02, 2012 8:46 pm

How to Close open documents?

Post 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

jvierra
Posts: 14543
Joined: Tue May 22, 2007 9:57 am
Contact:

How to Close open documents?

Post 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

User avatar
tech_soul8
Posts: 26
Joined: Mon Apr 02, 2012 8:46 pm

How to Close open documents?

Post 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 :)

Locked