Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Solved
Close all open Excel workbooks?
Posted on 2008-06-10
Hello all,
I am trying to figure out a way to close all open excel workbooks with VB. The code I have selects the first instance of Excel and closes all workbooks within that, but if there is more than one instance of Excel then they do not close as well. Either closing the workbook or quitting the application is fine.
Stopping the excel.exe process does stop Excel, but it does not stop it gently so I am not looking for that.
Thanks a bunch.
I am trying to figure out a way to close all open excel workbooks with VB. The code I have selects the first instance of Excel and closes all workbooks within that, but if there is more than one instance of Excel then they do not close as well. Either closing the workbook or quitting the application is fine.
Stopping the excel.exe process does stop Excel, but it does not stop it gently so I am not looking for that.
Thanks a bunch.
set objOffice = GetObject(,"Excel.Application")
For Each WBook In objOffice.Workbooks
WBook.Saved = True
WBook.Close
Next
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
4
10 Comments
How about this?
Dim objOffice As Excel.Application
Set objOffice = GetObject(, "Excel.Application")
do
objOffice.Quit
Set objOffice = GetObject(, "Excel.Application")
loop until err
set objOffice = nothing
Active today
Or, I haven't tested this, but what about.....
set objOffice = GetObject(,"Excel.Application")
For Each WBook In objOffice.Windows
WBook.Saved = True
WBook.Close
Next
Thanks for the try mates, but neither works.
The first script goes for an infinite loop without closing Excel and the second does not seem to have any effect.
Revisions or new ideas welcome.
The first script goes for an infinite loop without closing Excel and the second does not seem to have any effect.
Revisions or new ideas welcome.
Active today
Try this.
Regards,
Rob.
Regards,
Rob.
Set objOffice = GetObject(,"Excel.Application")
objOffice.DisplayAlerts = False
For Each objWindow In objOffice.Windows
objWindow.Activate
Set WBook = objOffice.ActiveWorkbook
WBook.Save
WBook.Close
Next
objOffice.DisplayAlerts = True
objOffice.Quit
Hi Rob,
It did not work. I placed a wscript.echo statement in the middle of the FOR loop to see if it ever activiated and it did not.
Thanks.
It did not work. I placed a wscript.echo statement in the middle of the FOR loop to see if it ever activiated and it did not.
Thanks.
Active today
That's odd....it worked with my Excel 2003.....which version are you using?
As you open new workbooks, does it open new windows, or open them all in all in one?
Rob.
As you open new workbooks, does it open new windows, or open them all in all in one?
Rob.
I am running Win XP and Office 2003.
In order to be thorough, I used workbooks in the same window and workbooks in different windows.
I am very curious as to why it worked for you and not me.
Thanks for your efforts so far.
In order to be thorough, I used workbooks in the same window and workbooks in different windows.
I am very curious as to why it worked for you and not me.
Thanks for your efforts so far.
Active today
OK, so I just realised that I was testing with *one* instance of Excel, but mutiple workbooks opened under that same instance.....
I then tested by running "Excel" separately a few times, and only one closed!
So, let's try something new, like looping through the Excel instances *and* windows, until it errors...
Regards,
Rob.
I then tested by running "Excel" separately a few times, and only one closed!
So, let's try something new, like looping through the Excel instances *and* windows, until it errors...
Regards,
Rob.
On Error Resume Next
While Err.Number = 0
Set objOffice = GetObject(,"Excel.Application")
objOffice.DisplayAlerts = False
For Each objWindow In objOffice.Windows
objWindow.Activate
Set WBook = objOffice.ActiveWorkbook
WBook.Saved = True
WBook.Close
Next
objOffice.DisplayAlerts = True
objOffice.Quit
Set objOffice = Nothing
WScript.Sleep 2000
Wend
MsgBox "Done"
Featured Post
An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month10 days, 6 hours left to enroll
722 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.