Use Scheuled Tasks to fire Event Code

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I did a little searching and didn't find much on this one. Can I use
Scheduled Tasks to run a snippet of code, which is a private sub, behind a
sheet? If so? How is it done?

Thanks,
Ryan--
 
I've used Scheduled Tasks to open a workbook and run the code in the
"ThisWOrkbook" module as Workbook_Open. You may have to self sign the code
so that it opens without the "Enable Macros" prompt.
 
I created a .vbs file to autorun one workbook, and set up a scheduled task to
run my vbs every morning at 9am. Here is the contents of the vbs file:

strUserIn = MsgBox("Run the PO file?",vbYesNo,"Automated Prompt")
If strUserIn = 6 then
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and
Settings\MyUserID\Desktop\PO analysis\opv.xls")
objExcel.Visible = True
objExcel.Run "CrunchIt", "SkipEmailPrompt"
objExcel.ActiveWorkbook.Save
'objExcel.ActiveWorkbook.Close(0)
'objExcel.Quit
end if

CrunchIt is my main macro, and "SkipEmailPrompt" is an optional parameter
because when I am running this daily I don't want to be prompted as to
whether or not to generate automated emails to my user group, I only want to
do that if I am running that macro manually.

Note that when I do it this way, I do not get a macro warning prompt, the
vbs just opens the workbook and the macro starts running (well, only if I
answer yes to the prompt, but you could take that out if you needed to).

I commented out the close/quit lines because I like to look at the updated
graphs each time it runs, but your needs might be different.

HTH,
Keith
 
Back
Top