Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A Better Way (XP; SP3)

    I have various Excel files when opened run some code via Auto_Open.

    What I am challenged with is retrieving/opening the files and having the Auto_Open code of the file run before processing the next line of code.

    Example of what I am trying to do:
    Sub MyCode()
    Open file
    Run some code on file just opened
    Save changes to file that was just opened
    End sub

    I tried putting a "DoEvents" after the line "Open file" but that did not work.

    Your suggestions are appreciated.
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A Better Way (XP; SP3)

    Put the code in the Workbook_Open event of the file being opened instead of in an Auto_Open macro. The Workbook_Open event code must go into the ThisWorkbook module:
    - Double click ThisWorkbook in the Project Explorer.
    - Select Workbook from the Object dropdown list in the upper left of the module window.
    - Copy or move the code from the Auto_Open macro into the Workbook_Open event procedure.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A Better Way (XP; SP3)

    Another way is:

    Workbooks.Open("SomeFile.xls").RunAutoMacros xlAutoOpen

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Better Way (XP; SP3)

    Is that code in the Auto_Open routine, or is it in the Open Event routine. If you are using Auto_Open, then the Auto_Open routine is NOT run if the file is opened from another workbook using VBA code. You would need to do something like this:

    <code>
    Sub MyCode()
    Dim oWB As Workbook
    Set oWB = Workbooks.Open(Filename:="C:MyDirMyExcelFile.xls")
    oWB.RunAutoMacros (xlAutoOpen)
    'Run some code on the file
    oWB.Save
    End Sub
    </code>

    If you are using the Open Event Routine, then the event routine should be complete before control returns to the VBA code that did the open.
    Legare Coleman

Posting Permissions

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