Schedule a macro on a specific time

[farid2001]

Dear Gentlemen

I have a file that contains about 100 workbooks
I would like to have code that opens a workbook at 1:00 am, runs my macro,
and when it finishes with that workbook, closes the workbook and opens the
next workbook, runs my macro, closes the workbook, and opens the next one and
so on.

Each workbook allready has a workbookopen code that runs my macro.
I also have a main workbook in that file that contains the complete Path of
every workbook.

Your help will be greatly appreciated.

Thanks & regards
farid2001

 

[smartin]

Dear Gentlemen

I have a file that contains about 100 workbooks
I would like to have code that opens a workbook at 1:00 am, runs my macro,
and when it finishes with that workbook, closes the workbook and opens the
next workbook, runs my macro, closes the workbook, and opens the next one and
so on.

Each workbook allready has a workbookopen code that runs my macro.
I also have a main workbook in that file that contains the complete Path of
every workbook.

Your help will be greatly appreciated.

Thanks & regards
farid2001

Well it sounds like you've done the part that can be done with Excel. To
open a workbook at 1AM you maybe can use Windows Scheduler/Tasks.

One caveat though, if you require the workbooks to be processed in
sequence (1 must finish before 2 updates, 2 must finish before 3
updates, etc.) then things are more complicated. I think a CMD ("DOS")
batch can be told to wait until a process completes before executing the
next command, but it's been a while since I've played with that.

 

[ryguy7272]

http://www.iopus.com/guides/winscheduler.htm

HTH,
Ryan---

 

[Patrick Molloy]

you can easily create a vbs (vbcript) file that instantiates a workbook,
calls a procedure (you can pass parameters too) then close the workbook
this way you can control when the subsequent workbooks are opened.
you can call the vbs file from the windows scheduler.

in this exampel i have created an excel workbook with a procedure named
DemoRoutine.

I create a text file with the following text, and i changed the extension
from .TXT to .VBS on my desktop. when i double click this vbs file, i can
see that the excel workbook opened and ran my code

option explicit
dim wb
dim xl
set xl = CreateObject("Excel.Application")
set wb = xl.workbooks.Open( "C:\temp\DemoBook4.xls")
xl.visible = true
xl.run "DemoRoutine"
wb.Close False
xl.quit
set wb = nothing
set xl = nothing

 

[farid2001]

Patrick

Thanks for your response.

What would the code be for openning a workbook, running my procedure, close
it, and open the next one?

Regards
farid2001

 

[farid2001]

Thanks to all for your help.

I used the task scheduler to determine the time I wanted to run the
procedure, Ryan's link was very helpful for this.

I used Patrick's vbscript example to work with the task scheduler, and
finally this is what I ended up with:

option explicit
dim wb
dim xl
set xl = CreateObject("Excel.Application")
set wb = xl.workbooks.Open _(
"C:\Users\farid\Documents\A1A_ms_cuentas\Macrpen_Workbooks.xlsm")
xl.visible = true
xl.run "OpenMyWorkbooks"
wb.Close False
xl.quit
set wb = nothing
set xl = nothing

The "OpenMyWorkbooks" procedure had the following code:

ChDir "C:\Users\farid\Documents\A1A_ms_cuentas"
Workbooks.Open Filename:= _
"C:\Users\farid\Documents\A1A_ms_cuentas\ms-4773161724.xlsm"

Workbooks.Open Filename:= _
"C:\Users\farid\Documents\A1A_ms_cuentas\ms-3289837307.xlsm"

Workbooks.Open Filename:= _
"C:\Users\farid\Documents\A1A_ms_cuentas\ms-5397491581.xlsm"

Workbooks.Open Filename:= _
"C:\Users\farid\Documents\A1A_ms_cuentas\ms-6317931847.xlsm"
End Sub

Also, each Workbook has a Private Sub Workbook_Open() procedure, therefore
Workbooks.Open Filename:= _
"C:\Users\farid\Documents\A1A_ms_cuentas\ms-3289837307.xlsm"
will not start until
Workbooks.Open Filename:= _
"C:\Users\farid\Documents\A1A_ms_cuentas\ms-4773161724.xlsm"
completely finishes its Private Sub Workbook_Open procedure and so on,
therefore it was not necesary to get entangled with the "DOS" instructions.

Thanks & regards
farid2001