Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 24th 05, 01:38 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2005
Posts: 6
Default 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


  #3   Report Post  
Old May 24th 05, 01:58 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2005
Posts: 6
Default 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   Report Post  
Old May 25th 05, 02:37 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2004
Posts: 4
Default 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   Report Post  
Old May 25th 05, 05:31 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2005
Posts: 6
Default 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   Report Post  
Old May 26th 05, 08:11 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2005
Posts: 6
Default 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   Report Post  
Old May 27th 05, 02:23 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2004
Posts: 4
Default 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   Report Post  
Old May 27th 05, 02:57 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2005
Posts: 6
Default 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)

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Batch file initiation from Excel macro? Yarroll Excel Discussion (Misc queries) 1 October 13th 06 02:50 PM
Call a batch file from an Excel Macro Alex Horan Excel Discussion (Misc queries) 0 March 2nd 06 03:29 PM
Call a batch file from an Excel Macro Gary''s Student Excel Discussion (Misc queries) 0 March 2nd 06 03:26 PM
Run Excel Macro in a Batch File DP[_3_] Excel Programming 2 September 8th 04 07:38 PM
VBA Code needed to start different public sub mbergman Excel Programming 0 February 5th 04 01:41 PM


All times are GMT +1. The time now is 10:00 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017