Running an Excel Macro as a Scheduled Task

[Dan Youngren via OfficeKB.com]

Hello all,

I'm trying to use the Windows Scheduled Tasks utility to run an .xls and a
certain macro within it. I've been able to run the .xls with no problem,
but not the macro. In Access, one can do it by adding /x macroname, but no
such luck with Excel, apparently.

I'd like to have the following macro, ExportAsHTMLAuto(), run through
Scheduled Tasks.

===

Sub ExportAsHTMLAuto()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "ExportAsHTMLAuto"

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"T:\Dan Youngren\System\Engineering.htm" _
, FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
Application.OnTime Now + TimeValue("00:00:05"), "Save_Exit"

End Sub

Sub Save_Exit()
Application.Quit
ThisWorkbook.Close SaveChanges:=True
End Sub

===

So, 5 seconds after Excel opens, it should save as html, then close 5
seconds later.

How do I get this to be run as a Scheduled Task?

 

[Tushar Mehta]

Two options come to mind to run a macro through the Windows scheduler.

(1) Use the workbook's _Open event procedure. For more search XL VBA
for 'Open event' (w/o the quotes).

(2) Schedule a VB script that instantiates XL through CreateObject(),
gets it to open the workbook, and runs the macro through the
Application's Run method.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions