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

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.

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.

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

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