Aaron Greene
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
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.
Use Application.Quit to exit excel application.
Use it just before End Sub in the sub routine where you are closing that workbook.
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.UnHideWorksh eets
Call rptProcedures.DocumentRepo rtStart
Call rptProcedures.SetDefaults
Call rptProcedures.Parameters_V alidateFol ders
Call rptProcedures.Parameters_R eportModes
'I added this line for demo purposed
wsParameters.Range("P_Repo rtMode").V alue = 0
Select Case wsParameters.Range("P_Repo rtMode").V alue
Case 0 'Development
Call rptProcedures.Parameters_A utoReportD ates
Call rptProcedures.Parameters_R eportNames
Case 1 'Auto-Run
Call rptProcedures.Parameters_A utoReportD ates
Call rptProcedures.Parameters_R eportNames
Call rptProcedures.CopyReportVa luesToRepo rt
Call rptProcedures.ExportReport
If wsParameters.Range("P_Auto Print").Va lue = True Then
Call rptProcedures.PrintReport
End If
Call rptProcedures.DocumentRepo rtComplete
Call rptProcedures.HideWorkshee ts
Call rptProcedures.SaveWorkbook AndClose
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?
Public Sub ProcessReport()
Dim wb As Workbook
Dim wsParameters As Worksheet
Set wb = ThisWorkbook
Set wsParameters = wb.Sheets("Parameters")
Call rptProcedures.UnHideWorksh
Call rptProcedures.DocumentRepo
Call rptProcedures.SetDefaults
Call rptProcedures.Parameters_V
Call rptProcedures.Parameters_R
'I added this line for demo purposed
wsParameters.Range("P_Repo
Select Case wsParameters.Range("P_Repo
Case 0 'Development
Call rptProcedures.Parameters_A
Call rptProcedures.Parameters_R
Case 1 'Auto-Run
Call rptProcedures.Parameters_A
Call rptProcedures.Parameters_R
Call rptProcedures.CopyReportVa
Call rptProcedures.ExportReport
If wsParameters.Range("P_Auto
Call rptProcedures.PrintReport
End If
Call rptProcedures.DocumentRepo
Call rptProcedures.HideWorkshee
Call rptProcedures.SaveWorkbook
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.
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.
Set wb = Nothing
Call rptProcedures.QuitExcel
End Sub
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.SaveWorkbook AndClose
You just want to use it for Save and don't close the excel as application.quit will close the excel on its own...
Call rptProcedures.SaveWorkbook
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.SaveWorkbook AndClose 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?
Call rptProcedures.SaveWorkbook
ASKER
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...
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
This will close excel completely...
Saurabh...