Application.DisplayAlerts=False & ActiveWorkbook.Saved=True Not Disabling Save Prompt at Close

[bruce42392]

I am using TaskScheduler to open a spreadsheet to run an Autpen subroutine. The Autpen subroutine calls various subroutines to pull data from a server, perform calculations, update charts, export pdfs, and send emails. At the end of Autpen, a timed popup allows user intervention to stop the program from closing OR automatically closes the program until TaskScheduler runs it the next day. My code worked fine for about 2 years, but now, Application.DisplayAlerts=False & ActiveWorkbook.Saved=True are not preventing the "Do you want to save before closing" prompt when Application.Quit is executed. The "save?" prompt does not appear for ActiveWorkbook.Close, but the Excel application window remains open after the workbook closes. I am using Excel 2010.

The ultimate goal is to have TaskScheduler open the spreadsheet, have the workbook update itself, and have the workbook/excel close.

Here is a copy of the code that is not working properly.

Application.DisplayAlerts = False
Application.Calculation = xlCalculationAutomatic
Select Case CreateObject("WScript.Shell").Popup("Do you want to close this workbook? No response will be interpreted as YES in 5s.", 7, "Close Workbook?", vbYesNo)
Case vbYes, 1, -1
If Workbooks.Count > 1 Then
ActiveWorkbook.Close SaveChanges:=True
Else
ActiveWorkbook.Save
Debug.Print "Saved = " & ActiveWorkbook.Saved
Debug.Print "Disp Alerts = " & Application.DisplayAlerts
Application.Quit
End If
Case vbNo
MsgBox ("You may close the workbook when ready.")
End Select
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub

 

[mrmmickle1]

Add in this line before the end of the subroutine:

[B][COLOR=#008000]'Guessing the line Application.Quit is not getting run...
'Try adding it right above end sub and see if that fixes your issue.
'Or put your code in a regular sub procedure and go through the code using F8 to 
'Determine why the Application.Quit is being skipped....
[/COLOR]
Application.Quit
[COLOR=#0000ff]End Sub[/COLOR]
[/B]

It should close excel.

 

[bruce42392]

I don't have "Application.Quit" directly before "End Sub" because I want the spreadsheet to remain open if the user selects "No" on the popup before 7s has passed.

The program works properly when I walk through the steps by pressing F8. If I allow the program to run, it throws back the "Do you want to save" message when it attempts to execute "Application.Quit". The immediate window confirms that Application.DisplayAlerts = False and ActiveWorkbook.Saved = True.

I also copied this section of code into a blank workbook on my desktop with no formulas or any other code. It closes without the "Do you want to save" message as desired and expected.

 

[mrmmickle1]

Sounds like you have a corrupt workbook.

 

[bruce42392]

I have never had a corrupt workbook and all my other subroutines are working correctly. What is also odd is that I have 8 spreadsheets running the same code, and they all started this behavior at the same time. For the last two years these spreadsheets have closed without the "Do you want to save" popup. The code was written in Excel 2010, and that is what I am currently using. It is frustrating that the program works correctly when debugging using F8 but will not work correctly when called to run. I am totally stumped here.

How to diagnose Excel file corruption and repair workbooks - Spreadsheet1 - Excel Data Analytics

 

[mrmmickle1]

Well if you have 8 workbooks with the same code then and it happened all at once you can probably rule the corruption issue out. Not sure where to go from here either. Maybe someone else will have some valuable input.

For Future Reference: A way I have found corrupt or crashed work books in the past is by looking in the file directory where they reside. If there is a .tmp file it's likely the workbook crashed....which can cause corruption.