Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch file to start multiple excel files with auto_open() macro. Delay code needed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch file to start multiple excel files with auto_open() macro. Delay code needed
On 23 May 2005 17:38:48 -0700, wrote in
microsoft.public.excel.programming and elsewhe 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 -- Michael Bednarek http://mbednarek.com/ "POST NO BILLS" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch file to start multiple excel files with auto_open() macro. Delay code needed
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch file to start multiple excel files with auto_open() macro. Delay code needed
On 24 May 2005 05:58:01 -0700, wrote in
microsoft.public.excel.programming: 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? -- Michael Bednarek http://mbednarek.com/ "POST NO BILLS" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch file to start multiple excel files with auto_open() macro. Delay code needed
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch file to start multiple excel files with auto_open() macro. Delay code needed
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch file to start multiple excel files with auto_open() macro. Delay code needed
On 26 May 2005 12:11:13 -0700, wrote in
microsoft.public.excel.programming: 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. -- Michael Bednarek http://mbednarek.com/ "POST NO BILLS" |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch file to start multiple excel files with auto_open() macro. Delay code needed
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) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch file to start multiple excel files with auto_open() macro. Delay code needed
On 26 May 2005 18:57:54 -0700, wrote in
microsoft.public.excel.programming: 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? -- Michael Bednarek http://mbednarek.com/ "POST NO BILLS" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Batch file initiation from Excel macro? | Excel Discussion (Misc queries) | |||
Call a batch file from an Excel Macro | Excel Discussion (Misc queries) | |||
Call a batch file from an Excel Macro | Excel Discussion (Misc queries) | |||
Run Excel Macro in a Batch File | Excel Programming | |||
VBA Code needed to start different public sub | Excel Programming |