Refresh data and exit with saving Macro Excel

问题:

I need to refresh the data from ole db source while opens excel and afte that saves and exit. Here is my macro code:

Sub auto_open()
    Call DataRefresh
End Sub

Sub DataRefresh()
    TimeToRun = Now
    Application.OnTime TimeToRun, "Refresh"
End Sub

Sub Refresh()
    ActiveWorkbook.Connections("Shas").Refresh
End Sub

Sub auto_close()
    Application.OnTime TimeToRun, "Refresh", , True
    Application.Quit
    ThisWoorkbook.Close SaveChanges:=True
End Sub

It's okay with renewing after openening but it doesnt exit. What am I doing wrong?

回答1:

You don't actually tell the workbook to close at any point...

Also, the Auto_Open() routine is technically triggered after the workbook has finished opening so you don't need to call separate routines:

Also Also - You can't close a workbook after you've quit the application...

Try just this instead:

Sub auto_open() ActiveWorkbook.Connections("Shas").Refresh DoEvents ThisWoorkbook.Close SaveChanges:=True End Sub