Simple VBScript - Autorun macro every night?

[Brad Okresik]

I want to run a macro in one of my excel files (on our server) every morning at 4am. I want the macro to runautomatically even when I am not logged in. How do I do this?

I have learned that I can create a .VBS file and use Control Panel > Scheduled Tasks to have the VBS file executed every morning. I have no clue how to write the 4 lines of VBS code that I need. I can show a messagebox with "msgbox("hello")". I need to open an Excel file, run the macro, and close the Excel file. It should be about three lines of code. I cannot figure out how to open an Excel file from the VBScript. I have been coding a fair amount of VBA and hae read extensivley about VBA coding. This should be simple, but I don't know where to start.

Thanks for any help,
Brad

[Paleo]

Hi Brad,

[VBA]
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set objNewJob = objWMIService.Get("Win32_ScheduledJob")errJobCreated = objNewJob.Create _
("Excel.exe", "********040000.000000-420", _
True , 1 OR 4 OR 16, , , JobID)
Wscript.Echo errJobCreated
[/VBA]

This script will run excel every monday, wednesday and friday at 04:00, then you can set at workbook_open to do what you want and close the file.

I hope this helps!

P.S.: This will work on:
Windows Server 2003: Yes
Windows XP: Yes
Windows 2000: Yes
Windows NT 4.0: Yes, with WMI installed
Windows 98: Yes, with WMI installed

[Anne Troy]

Sweeeeeeeeeeeet, Carlos!

[Paleo]

Couple questions. Aparently I put this text in a .VBS file and it will
automatically run itself on Mon, Wed, & Fri morning. It looks like this code IS
the scheduled task. So I wouldn't use the Control Panel > Schedule tasks GUI
with this code. Looks like this will open the Excel application. I suppose I
should put my macro in the workbook_open method of my personal.xls file?
Personal.xls is the only Excel file that will be opened by this code, correct?
The code is totally confusing to me so I am wondering how I can make it run
everyday (or atleast M-F) and how I can change the time at which it runs (for
testing purposes). I suppose there is nowhere on my computer where I can get
"Help" for these commands liek I can for VBA?

Thank you Paleo, i'll be tryin' this pretty soon!

Brad

Hi Brad,

yes, its already scheduling the task so you may forget about the scheduling manager.

the time is adjusted in this line:
[VBA]
("Excel.exe", "********040000.000000-420",
[/VBA]
where 040000 is the time (hours, minutes and seconds) you want the code to be executed. And of course you must change "Excel.exe" to your workbooks name. For Example:
[VBA]
("c:\xls\myWorkbook.xls", "********043000.000000-420",
[/VBA]
Will open the the workbook called myWorkbook.xls that resides on the folder xls at 04:30 am.

Exactly, your macro must be put at the workbook_open.

Yes, you may find help looking for WSH (Windows Script Hosting). This is the tecnology I have used here.

In order to make it work from monday to friday you must alter this line:
[VBA]
True , 1 OR 4 OR 16, , , JobID)
[/VBA]

to

[VBA]
True , 1 OR 2 OR 4 OR 8 OR 16, , , JobID)
[/VBA]

[Paleo]

Hi Brad,

so is it solved?

[Brad Okresik]

I couldn't get the code to work, but I did solve my problem.

I typed the code in a text document and saved it as a .vbs in one of the folders on the server. Maybe it is supposed to go in some specific location? Nothing happens at the scheduled time. If I double click on the VBS file I get an "Expected end of statement" error on line 4 character 56. So I tried putting a carrige return right before the "errJobCreated" on line 4, but this didn't work either. I noticed that after my change I did not get the error when I double clicked the VBS file. Instead I got a message box with only the number "2" in it? Oddly enough it was 2 minutes from the time I saved the file til the scheduled run time, but I think this was just a coincidence. I tested it on Monday morning so it shouldn't be the days that are causing it not to work.

Anyways the solution to my problem is simple. I can just put my macro in the open_workbook subroutine of an xls file and use the Windows GUI to "Schedule a task" (in the Control Panel) such that the workbook opens every weekday morning. I can enter my login and password so that the scheduled task will take place even if I am not logged in. When I first tested this while not logged in, I accidentally left the file and PERSONAL.xls hanging open. Now if I login I still can't get write access to these files becuase they are being used (by myself!). I will get the network admin to close the files for me. For the future I include the workbook.close method to close PERSONAL.xls and then the file in use. This should stop the problem. Is there any way to close the Excel application window from VBA? Probably not. Atleast I can't find one. It is really not necessary, just wondering.

Thanks for the discussion, it helped me realize how to get what I wanted. Stillnot sure how that code up there works. I understand that the days of the week are a base 2 system. I just got a Windows Scripting Hosting book but I havn't used it yet. I don't think I need it, given what I have recently discovered.

Thanks for the help,
Brad

[mgi]

Hi,
I had the same experience...but I need a way around using Task Scheduler.... so I'd like to get this to work.

Please address the "End of Statement" error notice.
Also, does the vbs file need to be in a specific folder?

Please help

Stage two will entail a modification to get input from a user

1. Browse to target folder
2. Browse to target name
3. Dropdown for run time

...or... a similar script to guide a user thru Task Scheduler

Is that feasible?

Thanks


[quote=Brad Okresik]I couldn't get the code to work, but I did solve my problem.

I typed the code in a text document and saved it as a .vbs in one of the folders on the server. Maybe it is supposed to go in some specific location? Nothing happens at the scheduled time. If I double click on the VBS file I get an "Expected end of statement" error on line 4 character 56. So I tried putting a carrige return right before the "errJobCreated" on line 4, but this didn't work either. I noticed that after my change I did not get the error when I double clicked the VBS file. Instead I got a message box with only the number "2" in it? Oddly enough it was 2 minutes from the time I saved the file til the scheduled run time, but I think this was just a coincidence. I tested it on Monday morning so it shouldn't be the days that are causing it not to work.

[Brad Okresik]

I don't know how to get the code above to work, so I can't answer your questions. I do know of a different way that might help you. I have used Matlab timers with lots of success. Matlab simply implements a Java timer, so you could do the same with Java if you don't have a Matlab license. Use a Java timer that executes every minute and check the clock in the timer function. When the clock matches the time you're looking for, then launch your VB script or whatever function it is that you need to run. You just need to leave the Java application (or Matlab) running continuously.

Hope that helps,
Brad

[khaledocom]

Hi all,

I tried the attached but I'm still receiving error, Please advise.


strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set objNewJob = objWMIService.Get("Win32_ScheduledJob")errJobCreated = objNewJob.Create _
("C:\222\test.xls", "********043000.000000-420", _
True , 1 Or 2 Or 4 Or 8 Or 16, , , JobID)
Wscript.Echo errJobCreated