+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1

    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. #2
    Michael Bednarek
    Guest

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

    On 23 May 2005 17:38:48 -0700, [email protected] wrote in
    microsoft.public.excel.programming and elsewhere:

    >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. #3

    Re: 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. #4
    Michael Bednarek
    Guest

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

    On 24 May 2005 05:58:01 -0700, [email protected] 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. #5

    Re: 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.


  6. #6
    Michael Bednarek
    Guest

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

    On 25 May 2005 09:31:54 -0700, [email protected] wrote in
    microsoft.public.excel.programming:

    >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?

    --
    Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

  7. #7

    Re: 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. #8
    Michael Bednarek
    Guest

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

    On 26 May 2005 12:11:13 -0700, [email protected] 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. #9

    Re: 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. #10
    Michael Bednarek
    Guest

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

    On 26 May 2005 18:57:54 -0700, [email protected] 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1