Hello,
I am trying to write a macro in Excel 2016 to refresh a Get & Transform Query on opening a file, then SaveAs .csv (UTF8), then close the application.
This is designed to run overnight to capture all the changes made to a separate 'Work in Progress' spreadsheet used by a team which is then uploaded to a website. I have a batch file which opens my file with an entry in Task Scheduler that works fine. The query is set to refresh on opening with 'Enable Background Refresh' unticked.
My code is below (I am no coder this has been cobbled together from various sources). When the file opens, the macro runs ok, csv file is written and the application closes, but the query has not updated. So the question is, how can I delay the 'SaveAs' part of the macro to the point where the query has completed refreshing? FYI query takes about 5 seconds to update at current size.

Thank You

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

shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False 'overwrite without asking
wbkExport.SaveAs Filename:="S:\Files\MyFile" & ".csv", FileFormat:=xlCSVUTF8, Local:=True
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False
ThisWorkbook.Save
Application.Quit
End Sub