Link to home
Start Free TrialLog in
Avatar of Aaron Greene
Aaron GreeneFlag for United States of America

asked on

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
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

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...
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.
Avatar of Aaron Greene

ASKER

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?
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.
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...
@ 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?
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.
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...
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial