how to close excel with macro/VBA

[jcaulder]

Hi,

I am using Excel to query a database and then publishing the results to an html document. I have Excel set to refresh the data everytime it is opened. I am then using a scheduled task in Windows to open the Excel spreadsheet nightly thereby refreshing the data.

This works fine except that the published html page is only updated AFTER the Excel worksheet is saved with the refreshed data. So I'm assuming I need to somehow use VBA to close Excel and save changes immediately after it is opened by the scheduler.

Any ideas on how to do this or perhaps a cleaner method all around for accomplishing this?

Thx

 

[FaneDuru]

In the "Workbook_Open" event from "ThisWorkbook" of the file you have to put the code:

Private Sub Workbook_Open()
 Application.OnTime Now + TimeValue("00:01:30"), "Closing_Excel"
End Sub

And in one module it has to be the next code:

Sub Closing_Excel()
 ThisWorkbook.Save
 Application.Quit
End Sub

You have to set some time to excede the necessary one for refreshing. In this case "Closing_Excel" runs after one minute and 30 seconds...

I hope this helps...
Fane Duru'

 

[jcaulder]

I had to change the "Closing Excel" to "ThisWorkbook.Closing_Excel" for some reason. It was looking for c:\spreadsheet_name.xls'!Closing_Excel' and was erroring out before. Perhaps where I defined the subroutine?


Regardless, works PERFECT now!!

Many thanks for the help!!