Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Auto_Opens from macros (Excel 2002)

    A macro i was writing needed to open a couple of spreadsheets, run their Auto_Open macros and then do some further processing.

    I found that, whatever I did, the Auto_Open macro only ran on the first file to be opened. In the Knowledge Base, I found an article Q157308 (about Excel 97!!) that confirmed that only one 'Auto' action can be run in each macro and suggested a couple of workarounds. One used the Application.OnTime method, the other Application.Run.

    I never succeeded in getting the Application.Run method to work, but did have some success with the OnTime method, which involves putting the RunAutomacros commands in separate procedures and running them using timed statements. Although it worked, however, the timing was tricky and, if one procedure took longer than anticipated, a later one sometimes ran first, causing them to run in wrong sequence. The only alternative was to create a 'safety margin', which resulted in pauses between each procedure.

    I got round this by 'cascading' the procedures - Procedure 1 calls procedure 2, which calls procedure 3 and so on. The only problem is that, without getting involved in some fancy parameter-passing, it's difficult to re-use them in order to change the files they operate on and the procedures they call next.

    I'm frustrated that this behaviour appears to have been around since Excel 97 and has never been fixed. Or have I missed something? There's got to be an easier way, surely?

    I'd appreciate help anyone can give me on this.

    Mike

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Auto_Opens from macros (Excel 2002)

    Mike,

    What was the problem with the Application.Run?

    Something which might be possible is to set something in one procedure which leaves a mark which may be tested. For instance, Proc 1 might create a dummy file, di its bit, and delete the file. If you're sure that Proc2 starts after proc1, it might be able to test for the non-existance of the dummy file created and deleted by proc1 to execute.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Auto_Opens from macros (Excel 2002)

    Thanks, Geoff

    The syntax for Application.Run was given as Application.Run ActiveWorkbook.FullName & "!Auto_Open",
    Fullname being the full path and file name. The problem was that I got a 'not found' message (despite it just having opened the same file). To be honest, I only tried it once, then went back to the OnTime method. If you think that normally works, I'll try it again.

    Re leaving a mark, that's a useful idea, and I'll look at it. All of this seems a complicated way of doing what ought to come naturally, though, doesn't it?

    Mike

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Auto_Opens from macros (Excel 2002)

    Mike,

    The problem with the Application.Run is that it is open. The syntax you have is for running a macro from a workbook which is not open. Once it's open, try:
    Application.Run AutoOpen
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Auto_Opens from macros (Excel 2002)

    OK, understood - I'll give it a go.

    Thanks for all your help.

    Mike

Posting Permissions

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