VBA Code to Save and Close workbooks opened by multiple excel processes

I have a case where an individual opens multiple EXCEL sessions rather than multiple workbooks within one session for the simple reason that one session (as of Office 2007) will not let him display different spreadsheets on multiple screens.

The problem that we are trying to resolve is to how close out some workbooks that are opened without knowing the specific session used to  open them. I read another case on EE where the requester tried to do similar and the conclusion was that it cannot be done. Surely that cannot be true. REF: VBA loop through each excel application submitted 7/23/2009 @ 3:58 PM
rpbishipAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rpbishipAuthor Commented:
It should be added that the intent of the project is to automatically run the routine at the close of the day without user intervention.
Roy CoxGroup Finance ManagerCommented:
I've put this code together after a bit of research.

Option Explicit

Public Sub Close_All_Excel()
    Dim objExcel As Object
    On Error Resume Next
     Do
         Set objExcel = GetObject(, "Excel.Application")
         If Err.Number = 0 Then
             objExcel.DisplayAlerts = False
             objExcel.Save
             objExcel.Quit
             Set objExcel = Nothing
             DoEvents
         Else
             Exit Do
         End If
     Loop
End Sub

Open in new window


You could use an OnTime method to run the code at a specific time or maybe Windows Scheduler
Roy CoxGroup Finance ManagerCommented:
To run the macro at a specific time the workbook will need to remain open . Place this code in the WorkBook Open event

Private Sub Workbook_Open()
'rum at 6PM
    Application.OnTime TimeValue("18:00:00"), "Close_All_Excel"
End Sub

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

rpbishipAuthor Commented:
Roy,
I tested the code and it is almost perfect. What I find is that workbooks opened under the first Excel process (Application) closed, but others did not.
Roy CoxGroup Finance ManagerCommented:
I opened three instances of Excel and all closed. I'll test it again
Roy CoxGroup Finance ManagerCommented:
I'm running Excel 2016 and I have just found out that I cannot open multiple instances of Excel as in Excel 2007. However, when I do there is one instance left. I'll keep experimenting.
Roy CoxGroup Finance ManagerCommented:
I've spent some time and no code that I can find works from Excel. I have found some VB Script that seems to work OK.

Open several instances of Excel then double click the attached file.

If this is suitable you will need to set up task with Windows Scheduler
CloseAllExcel.vbs
rpbishipAuthor Commented:
Will test on Monday. Thank you for your effort.
Roy CoxGroup Finance ManagerCommented:
No problem
rpbishipAuthor Commented:
Roy,
The script does indeed terminate all open Excel sessions. It does not, however, meet the criteria of saving open workbooks nor does it really close them. It simply kills the Excel processes which in effect shut them down. Upon re-opening Excel, presumably the next morning, One is given the option to recover documents, which do not reflect any changes made that were not auto saved. 3 Files attached to illustrate. The first is a sample changed document, the second showing the documents to recover, the last being the document that was changed in recovery mode.

I think at this point, I am going to pursue a different tack with respects to the workbooks. You help was very much appreciated. I do not believe you need to continue with this.
changed-date.png
Excel-reopen.png
Recovered-Document.png

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
It was interesting, sorry it didn't work.
rpbishipAuthor Commented:
No solution for the problem was found.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.