Running an Excel Macro overnight 1

[funkmonsteruk]

I need to run an excel macro overnight.

I'm thinking of adding it as a scheduled task but don't know how to run a macro from a command line.

 

[RobBroekhuis]

On the command line, just instruct Excel to start up with a certain workbook, and call the macro from the Workbook_Open event handler (on the ThisWorkbook code sheet).
Rob

 

[funkmonsteruk]

This works fine, but i need to access the workbook without updating the information at a later date

 

[Pcfred]

F.Y.I.
If you hold the shift key down while opening the workbook in excel the auto macros will not run.

 

[KimVong]

Funkmonsteruk
I do want to do the same, and I have no idea what you guys were mentioning up there, can you give me a code to run the macro at night? I do want to make a schedule for it also.
Please help.
thanks

 

[RobBroekhuis]

I could have sworn I replied again to this thread - apparently it didn't go through. My suggestion was to start up Excel with a different workbook than your main one, which has a workbook_open event that opens your main workbook and calls the macro you would like to execute. I've not tried that kind of thing, but see no reason why it wouldn't work.
Rob

 

[funkmonsteruk]

KingVong,

To run the macro at night you need to add it as a scheduked task to your server or PC.

Steps are as follows:

You need to add a routine that you want to take place to the autoopen routine of your workbook, the code i am using is as follows:

Private Sub Workbook_Open()
Dim mydate As Date

mydate = Date

Sheets("sheet1&quot.Activate
Application.Run ("BQ_UpdateWorkbook&quot
Sheets("sheet1&quot.Copy
ActiveWorkbook.SaveAs ("F:\Process Support\Taps Balances\Taps Balances Report_" & Format(mydate, "dd-mm-yyyy&quot)

application.quit
End Sub

This particular routine updates information from a business query workbook, copies the first sheet and paste it into a new workbook. It then quits excel.

To run this overnight you need to add two things to the scheduled task command line - firstly the excel.exe file (if you go through the scheduled task wizard this should be inserted when you select excel as the scheduled programme)

After this go to the command line and after the excel.exxe file enter the full file path of the individual excel file you wish to run eg:

"D:\Program Files\Office\Excel.exe" "F:\Business Objects\Documents\Bank Balances reports\bene_acct_trans_post_title.rep"

This should now open the required file at the specified time, which will invoke your autoopen routine and perform whatever task you have requested

 

[KimVong]

Funkmonsteruk
I'm sorry, I still don't quite understand, what should me in exel.exe?let's say I just want to run the macro, I don't need it save or copy to anything.
This is what I need,
open my excel macro, and type in ctrl-shift-A, and then my macro will run, and that's all I need it to do. Then the next day, I will come in and shut it off. In this case,how should I approach it using your code?
your help is greatly appreciated.

Kim

 

[RobBroekhuis]

If what you're doing is just a one-off, not a regularly scheduled thing, consider just setting an OnTime event:

Application.OnTime timevalue("2:00:00&quot,"MyMacro"

You can type this command in the debug window of the VBE before you leave for the night, and the macro will run at 2am.
Rob

 

[funkmonsteruk]

OK let's break it down, If you wnat to run something overnight then add it as a scheduled task on your PC.

A sceduled task will run a program, in this case Excel, it does this by calling a file called the Excel.exe file, this is usually located within your programme files on your local hard drive.

To get a scheduled task to open a particular file, you must type the name of the file in the command line after the excel.exe file eg: "D:\Program Files\Office\Excel.exe" "F:\Business Objects\Documents\Bank Balances reports\bene_acct_trans_post_title.rep"

as you can see the command line from the scheduled task first asks your computer to start excel, then it specifies the relevant file you want to access.

The purpose of adding your macro to the autoopen routine is that it will run everytime your document is opened. So if the task scheduler opens a particular document, any item in it's autoopen routine will be run.

Lets say that you have recorded a macro which you wnat to run overnight, if you go to edit the macro copy it's body from it's current location (usually a module in your workbook) to the autoopen sub procedure of your workbook (to access this doubleclick on Workbook item from the project explorer whilst in VBA editor and from the left hand drop down box select workbook and from the right hand select autoopen)

You will see two lines of code:
Private Sub Workbook_Open()
End Sub

Your macro goes between these and will run every time the workbook is opened. Or at least thats the theory......

 

[KimVong]

hi all
I do understand the schedule task on my PC now, I just did a schedule,
Now you mention that I need to add a function on my VB editor. I have two days I wanted my macro to auto run.
One tab is Monday(if you click on tuesday, it will run or ctrl +shift+M ) and one tab is Thursday(if you click on thursday, it will run or ctrl +shift+T)
what should I put on my function for this macro to run at night?
please help.
thanks

Kim