Auto_Open Macro showing unwanted prompt

[dp39darren]

Hi all,
I have an auto_open macro which saves file as a csv, then closes the application. The file is opened using Task Scheduler every night. Until recently this worked fine, but now when it runs it shows save changes prompt and fails to close Excel. I've not changed the macro so at a loss to know why this no longer works or how to fix. many thanks.
Prompt.jpg

Public Sub Auto_Open()

Dim wbkExport As Workbook
Dim shtToExport As Worksheet

Set shtToExport = ThisWorkbook.Worksheets("Sheet2")     'Sheet to export as CSV
Set wbkExport = Application.Workbooks.Add

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False
wbkExport.SaveAs Filename:="file path" & ".csv", FileFormat:=xlCSVUTF8, Local:=True
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False
ThisWorkbook.Save
Application.Quit
End Sub

[huuthang_bd]

Try use below line instead of ThisWorkbook.Save

ThisWorkbook.Close True

[dp39darren]

This gets rid of the prompt, but leaves Excel open. This is better, but still not sure why I can't close excel and avoid the prompt. Thanks.

[dominicb]

Good morning dp39darren

You are not telling Excel to close, you are forcing only the workbook to close - which it does.

Use this command to completely close Excel.

Application.Quit

HTH

DominicB

[huuthang_bd]

Try this

ThisWorkbook.Save
ThisWorkbook.Saved = True
Application.Quit

[dp39darren]

I still get the prompt. thanks.

[huuthang_bd]

How about this:

Application.DisplayAlerts = False
Application.Quit

[dp39darren]

I've tried solutions with Debug & Step Into and it works fine, but not when running, so maybe something else?

[dominicb]

Hi dp39darren

Can you delete all the data (we don't need to see any of that) and post the workbook?



Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[dp39darren]

I think I have attached a file.
This likely won't work as there is a Query which connects to a file on local network.
Desired result is:
Open Workbook > Refresh Query > Save sheet as .csv > Save Excel > Close Excel
This happens from a scheduled task, so no manual intervention. As per original post, this worked until recently. thanks for your help.

[huuthang_bd]

Try again

ThisWorkbook.Save
Application.DisplayAlerts = False
Application.EnableEvents = False
ThisWorkbook.ChangeFileAccess xlReadOnly
Application.Quit

[dp39darren]

That works. Thanks for your help.