Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Schedule a macro on a specific time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Schedule a macro on a specific time
farid2001 wrote:
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Schedule a macro on a specific time
http://www.iopus.com/guides/winscheduler.htm
HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "smartin" wrote: farid2001 wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Schedule a macro on a specific time
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Schedule a macro on a specific time
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 "Patrick Molloy" wrote: 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Schedule a macro on a specific time
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\Macro_Ope n_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 "Patrick Molloy" wrote: "farid2001" wrote in message ... 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 "Patrick Molloy" wrote: 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 have a wb property set to False until the procedure finishes, then set it to True xl.run "DemoRoutine" do until wb.MyPrioerty=True doEvents Loop wb.Close False set wb = xl.workbooks.Open( "C:\temp\someotherbook.xls") xl.run "AnotherRoutine" xl.quit set wb = nothing set xl = nothing "farid2001" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is there a way to schedule a macro to execute at a certain time? | Excel Discussion (Misc queries) | |||
Can I schedule a macro to run automatically at a certain time? | Excel Worksheet Functions | |||
Running a macro on a specific time | Excel Programming | |||
Time Stamp for specific macro.. | Excel Programming | |||
Please help...I need a looping macro at a specific time | Excel Programming |