On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
Course Of The Month
8 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
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.
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.
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.
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
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.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 17 hours left to enroll
615 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.