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.
This topic is 11 years and 11 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
tech_soul8
Posts: 26
Last visit: Sat Mar 08, 2014 12:14 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
Last visit: Sat Mar 08, 2014 12:14 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: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

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
Last visit: Sat Mar 08, 2014 12:14 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 :)
This topic is 11 years and 11 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked