Excel workbook not closing properly when closed with vba

I have an excel report that is automatically triggered to run either by a vbscript file or by a Wonderware HMI.  The report is setup so that it will execute macros when it opens and close itself when the code is completed.  Everything works the way I expect it to, except that the excel application remains open after the workbook is closed.  I have attached a copy of the workbook.  Is there any way for me ensure that excel is shutdown.eesample.xlsm
Aaron GreeneProgrammerAsked:
Who is Participating?
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.

Saurabh Singh TeotiaCommented:
When you are entire macro has run..Just right at the end..end this line of the code...

Application.Quit

Open in new window


This will close excel completely...

Saurabh...
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Haven't downloaded your file but you may try something like this....

Use Application.Quit to exit excel application.

Use it just before End Sub in the sub routine where you are closing that workbook.
0
Aaron GreeneProgrammerAuthor Commented:
This is my main procedure that runs the report.  I call a procedure called "QuitExcel" as part of the procedure.  

Public Sub ProcessReport()
Dim wb As Workbook
Dim wsParameters As Worksheet

Set wb = ThisWorkbook
Set wsParameters = wb.Sheets("Parameters")
Call rptProcedures.UnHideWorksheets
Call rptProcedures.DocumentReportStart
Call rptProcedures.SetDefaults
Call rptProcedures.Parameters_ValidateFolders
Call rptProcedures.Parameters_ReportModes
'I added this line for demo purposed
wsParameters.Range("P_ReportMode").Value = 0
Select Case wsParameters.Range("P_ReportMode").Value
Case 0 'Development
Call rptProcedures.Parameters_AutoReportDates
Call rptProcedures.Parameters_ReportNames
Case 1 'Auto-Run
Call rptProcedures.Parameters_AutoReportDates
Call rptProcedures.Parameters_ReportNames
Call rptProcedures.CopyReportValuesToReport
Call rptProcedures.ExportReport
If wsParameters.Range("P_AutoPrint").Value = True Then
Call rptProcedures.PrintReport
End If
Call rptProcedures.DocumentReportComplete
Call rptProcedures.HideWorksheets
Call rptProcedures.SaveWorkbookAndClose
Call rptProcedures.QuitExcel
Case 2 'On Demand
OnDemand.Show
End Select


Set wsParameters = Nothing
Set wb = Nothing
End Sub


Public Sub QuitExcel()
Application.DisplayAlerts = False
Application.Quit
Application.DisplayAlerts = True
End Sub

I am using "Application.Quit" as part of the QuitExcel procedure.  Should I just put that at the end of my main procedure?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Call that procedure again in the end after the End Select.
Set wb = Nothing
Call rptProcedures.QuitExcel
End Sub

Open in new window


EDIT: Because right now you are using it with the Case 1 within the Select Case block so it will only be executed if the control passes to the Case 1.

Same way you may also use this inside the Case 0.
0
Saurabh Singh TeotiaCommented:
I'm assuming in this code

Call rptProcedures.SaveWorkbookAndClose

You just want to use it for Save and don't close the excel as application.quit will close the excel on its own...
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@ Saurabh

Call rptProcedures.SaveWorkbookAndClose is also being called within Case 1, so it will also get executed only once the control passes to Case 1 but what if the control doesn't pass to case 1 and rather passes to either case 0 or case 2?
0
Aaron GreeneProgrammerAuthor Commented:
Under normal operation, the workbook will read values from a set of text files that provide the date to be reported, a true/false value that determines whether to print the report and the mode of operation.  0 indicates that the report is to open in development mode.  In dev mode the report reads the report date and validates the existence of target folders.  2 indicates a manual reporting mode where the user selects the report date with a user form.  1 is automatic mode.  In automatic mode, the report performs all of the procedures without user input and then closes.  1 is the standard mode of operation.  The report will use the QuitExcel procedure at the end of manual operation.
0
Saurabh Singh TeotiaCommented:
Aaron,

Since you are more connected to this process what i will do is wherever i have wb.close post save as in closing my excel workbook after saving..I will use application.quit as that will do the same thing but the only difference will be rather then closing workbook and letting the excel instance open..It will close the excel instance which will close the workbook automatically..which is basically what you are looking for..

Saurabh...
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Actually when you call rptProcedures.SaveWorkbookAndClose, the next call to rptProcedures.QuitExcel doesn't take place because during rptProcedures.SaveWorkbookAndClose, the workbook is actually closed,

So my suggestion in this case would be to remove rptProcedures.QuitExcel and amend your rptProcedures.SaveWorkbookAndClose sub routine like this.....


Public Sub SaveWorkbookAndClose()
Dim wb As Workbook
Application.DisplayAlerts = False
Set wb = ThisWorkbook
wb.Save
Application.Quit
End Sub

Open in new window


See if the above change does what you are trying to achieve.
0

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
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
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.