Batch file to start multiple excel files with auto_open() macro. Delay code needed

[pierrelap]

Hello,

I have multiple excel files I wish to open in sequence. They all have
auto_open() macros so they update, save and close. My problem is that
when I put them in a batch file, all my excel files open at the same
time.

Any idea?

P.S.1 Calling different batch files from a main batch does not work.
The only thing the underlying batch has to do is start excel and when
it does, its job is done and the main batch goes on to the next
command.

P.S.2 I thought about delaying the second job with
PING 1.1.1.1 -n 1 -w 60000 >NUL
but I'd have to know how long each of my files takes to update

 

[Michael Bednarek]

Hello,

I have multiple excel files I wish to open in sequence. They all have
auto_open() macros so they update, save and close. My problem is that
when I put them in a batch file, all my excel files open at the same
time.

Any idea?

[snip]

Try this:
START "" /WAIT book1.xls
START "" /WAIT book2.xls
etc.

See: START /? or, under NT5.1, paste this into your Start/Run... box:
ms-its:%windir%\Help\ntcmds.chm::/start.htm

 

[pierrelap]

Thanks for the answer, unfortunately, START "" /WAIT does not work. The
Dos command is to start the excel file. Once it's done, the dos
command's job is done and it starts the second file.

 

[Michael Bednarek]

Thanks for the answer, unfortunately, START "" /WAIT does not work. The
Dos command is to start the excel file. Once it's done, the dos
command's job is done and it starts the second file.

Which of the requirements above does the command sequence I suggested
fail? What does it do instead?

 

[pierrelap]

Hello,

This is what I have in my batch file (as suggested by you):
path C:\
START "" /WAIT autoopen.xls
START "" /WAIT autoopen2.xls

The autoopen.xls and autoopen2.xls files have auto_open() macros that
start an update macro, and another one that saves and exit.

The batch file gou suggested starts both files one after another. In
other words, I have two excel files that update at the same time. The
wait command is only telling dos to wait until the file is opened.
After it is opened, it goes on even though the update, save, close
procedure is not finished.

What I need is to prevent having two files updating at the same time.

 

[Michael Bednarek]

Hello,

This is what I have in my batch file (as suggested by you):
path C:\
START "" /WAIT autoopen.xls
START "" /WAIT autoopen2.xls

The autoopen.xls and autoopen2.xls files have auto_open() macros that
start an update macro, and another one that saves and exit.

The batch file gou suggested starts both files one after another. In
other words, I have two excel files that update at the same time. The
wait command is only telling dos to wait until the file is opened.
After it is opened, it goes on even though the update, save, close
procedure is not finished.

What I need is to prevent having two files updating at the same time.

That's not how things work here (under NT5.1): here, I open the first
workbook - batch file sits and waits. I close the 1st workbook -
batchfile invokes the 2nd workbook and sits and waits. I close 2nd
workbook - batch file finishes.

Which operating system do you use and what's the auto_open VBA code?

 

[pierrelap]

I'm under Win2k

And here's my VA code:

Sub auto_Open()
Application.OnTime Now + TimeValue("00:00:03"), "update"
End Sub

'This is a Datastream update
Sub update()
Sheets("REQUEST_TABLE").Select
Application.Run "autoopen.xls!StartProcessingRT"
Application.OnTime Now + TimeValue("00:00:05"), "Save_Exit"
End Sub

Sub Save_Exit()
ActiveWorkbook.Close True
End Sub

 

[Michael Bednarek]

I'm under Win2k

And here's my VA code:

Sub auto_Open()
Application.OnTime Now + TimeValue("00:00:03"), "update"
End Sub

'This is a Datastream update
Sub update()
Sheets("REQUEST_TABLE").Select
Application.Run "autoopen.xls!StartProcessingRT"
Application.OnTime Now + TimeValue("00:00:05"), "Save_Exit"
End Sub

Sub Save_Exit()
ActiveWorkbook.Close True
End Sub

An analysis of that code tells me that Excel doesn't seem to exit after
opening autoopen.xls, and consequently the batch file should never get
to execute the second line (autoopen2.xls). To verify this, I created
two workbooks with these names, put your VBA code into those workbooks,
and created a batch file (test.cmd):
START "" /WAIT autoopen.xls
START "" /WAIT autoopen2.xls
Running that batch file produced exactly the expected results: the 1st
workbook opened, the VBA code executed, including closing the workbook,
and then Excel just sat there, and so did the batch file.

I had to close the first workbook manually to allow the batch file to
continue with the second workbook. In order to end the batch file, I had
to manually close the second workbook.

I think Datastream (AdvanceOffice.xla) has its own auto_open VBA module,
but I doubt that could be the cause of the behaviour you describe. I
might run a full test including Datastream updating over the weekend,
but I really doubt any benefit.

In the meantime, what happens if you run the following batch file:
START "" /WAIT CALC
START "" /WAIT NOTEPAD
Do the two applications execute strictly one-after-the-other or (almost)
simultaneously? They should do the latter and they do so here.

 

[pierrelap]

Hmm,

It works with
START "" /WAIT CALC
START "" /WAIT NOTEPAD

Notepad doesn't start before I exit calc....

You are right about Datastream. It is an axcel add-in that has a
workbook_open macro. This is why I have to use an auto_open.
Otherwise, my Datastream macros would not work when I enter the file
manually and press escape to cancel the auto_open. (I'm aware of
pressing shift, but MS disabled it in Office XP (or Win2k don't
remember)

 

[Michael Bednarek]

Hmm,

It works with
START "" /WAIT CALC
START "" /WAIT NOTEPAD

Notepad doesn't start before I exit calc....

You are right about Datastream. It is an axcel add-in that has a
workbook_open macro. This is why I have to use an auto_open.
Otherwise, my Datastream macros would not work when I enter the file
manually and press escape to cancel the auto_open. (I'm aware of
pressing shift, but MS disabled it in Office XP (or Win2k don't
remember)

As I wrote before, I can't see how the second workbook ever gets opened
by the batch code unless you manually close the first one. However, you
say it does. Therefore, I assume there is a connection between these two
workbooks that isn't obvious. What happens if you remove the line from
the batch file which starts the second workbook? Does it still show up?