PDA

View Full Version : Running an Excel Macro as a Scheduled Task



Danimagus
08-06-2005, 11:00 AM
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?

Helplesss
08-06-2005, 11:40 AM
Seacrhing memory banks...., rename your macro autoexec (i think???) which will automatically start the macro when you open the xls

Danimagus
08-06-2005, 11:49 AM
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:

rad_s4
09-06-2005, 05:03 PM
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

Parry
09-06-2005, 11:03 PM
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

rad_s4
10-06-2005, 06:22 PM
Nice one Graham; powerful and scary stuff these .VBS scripts - no wonder security levels get set to prevent them being propogated over networks.