Forum Home
Press F1
Thread ID: 58648	2005-06-07 23:00:00	Running an Excel Macro as a Scheduled Task	Danimagus (8260)	Press F1

Post ID	Timestamp	Content	User
361900	2005-06-07 23:00:00	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?	Danimagus (8260)
361901	2005-06-07 23:40:00	Seacrhing memory banks...., rename your macro autoexec (i think???) which will automatically start the macro when you open the xls	Helplesss (272)
361902	2005-06-07 23:49:00	Thanks for the reply, Helplesss. If i did that, though, the document would always open, save, then close within the space of 10 seconds every time I opened it. There needs to be some sort of condition for that, making it happen only at certain times or if opened in a certain way (ie, by the Task Scheduler). But, of course, I don't know how to do that. :badpc:	Danimagus (8260)
361903	2005-06-09 05:03:00	One approach is to have the macro check for the contents of a particular cell in between clock cycles as the timer counts down. If the cell is blank when the sheet opens and remains blank - the macro will continue to run - if an entry is input into the cell the macro can exit. This will allow an auto_open macro to run and complete as scheduled, but be interrupted by a user opening the workbook and keying an entry into the target cell. copy the following code into a module in a new workbook, filesave then reopen and enter anything into cell B1 to stop the timer. HTH Public NextTime As Date Public EndTime As Date Sub auto_open() EndTime = Now + TimeValue("00:00:15") NextTime = Now + TimeValue("00:00:01") ActiveSheet.Range("A1").NumberFormat = "hh:mm:ss" ActiveSheet.Range("A1").Value = EndTime - Now Application.OnTime NextTime, "Continuecount" End Sub Sub Continuecount() NextTime = Now + TimeValue("00:00:01") If EndTime - Now < 0 Then '*** call ExportasHTML macro here *** Exit Sub End If ActiveSheet.Range("A1").Value = EndTime - Now If Range("b1").Value > "" Then Exit Sub End If Application.OnTime NextTime, "Continuecount" End Sub	rad_s4 (7401)
361904	2005-06-09 11:03:00	Hi as an alternative approach you may wish to run a vb script. This requires no macros within the workbook at all as the script handles whatever is required. Heres an example where Im opening a separate instance of Excel (which wont be visible to the User) then opening a specific file then creating a htm file based upon that file. The benefits of a script approach is that the macro security prompt (which can get in the way of automation) doesnt happen as the code is outside Excel. The code below can be saved in a simple text file then the extension changed from txt to vbs. Double click to run or right click then Edit to edit the script. Of course you still need a way to run the vbs periodically using something such as the windows scheduler. Dim objExcel Dim objWorkBook Set objExcel = CreateObject("EXCEL.APPLICATION") Set objWorkBook = objExcel.Workbooks.Open("C:\test.xls") On Error Resume Next objExcel.DisplayAlerts=False objWorkBook.SaveAs "C:\test2.htm",44 objExcel.DisplayAlerts=True objWorkBook.Close True objExcel.Quit Set objWorkBook = Nothing Set objExcel = Nothing	Parry (5696)
361905	2005-06-10 06:22:00	Nice one Graham; powerful and scary stuff these .VBS scripts - no wonder security levels get set to prevent them being propogated over networks.	rad_s4 (7401)
1