Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to run after linked Excel file has updated (Excel 2000)

    I need to make some VBA code run when an Excel file is opened. Currently the code runs and then my linked Excel file is updated, so the code does not run on the correct data. How do I make the code run after the Excel file has been updated?

    Thanks,
    Justin K

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to run after linked Excel file has updated (Excel 2000)

    How is the update done? Can you automate the update so that the code runs after the update? Can you show us the code that runs?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to run after linked Excel file has updated (Excel 2000)

    A method I use to make sure my code runs on the right data is to put all the data in other workbooks and link back to it. I have processes that create different workbooks with lists of data to be used in my main workbook, the main workbook just links to these sheets. When the main workbook is opened, Workbook_Open() opens and hides all the linked workbooks and then the main workbook recalculated -- using all the new data from the linked workbooks. I have a field on the main workbook that checks the datestamps included with the other data and determines if all of the data has been updated. If this value checks out, then the rest of the processing can continue. Workbook_BeforeClose() closes all the supporting workbooks to keep things tidy when done.

    Some of the data comes in as direct links, pulling individual values; other cells in the main workbook use a =sumif formula to just bring a total into the cell.

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to run after linked Excel file has updated (Excel 2000)

    Put in code that runs your macro N seconds/minutes after the document opens, or after a particular boolean flag is set by the code in Workbookopen.

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to run after linked Excel file has updated (Excel 2000)

    The data in my files are just simple links: ='[Roster_Master_List.xls]Team Roster'!B176
    How would I go about adding a timer or variable?

    Thanks,
    Justin K.

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

    Re: Macro to run after linked Excel file has updated (Excel 2000)

    Try putting the code in the Worksheet Calculate event in the module behind the worksheet(s) than contain the links. That event should be triggered when the linked data is updated.
    Legare Coleman

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to run after linked Excel file has updated (Excel 2000)

    What Howard meant to say was:

    Put the code currently in your Workbook_Open sub into a sub in a NORMAL module. Like this:

    Sub ContinueOpen()
    'Code that used to be in Workbook_Open
    End Sub

    Now in your Workbook_Open sub in the Thisworkbook module:

    Private Sub Workbook_Open()
    Application.Ontime now+TimeValue("00:00:00"),"ContinueOpen"
    End Sub

    Change the time to 00:00:01 to delay 1 second
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to run after linked Excel file has updated (Excel 2000)

    Thank you everyone for your help!
    I'm going to try these things.

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

    Re: Macro to run after linked Excel file has updated (Excel 2000)

    If you don't reset the Ontime in the ContinueOpen routine, that is only going to execute the code once.

    It will also be much more efficient to use the Worksheet Calculate event.
    Legare Coleman

  10. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to run after linked Excel file has updated (Excel 2000)

    You coud try something like the following.

    <pre>Public Sub Auto_Exec()
    With Application
    ' Delay running the code for 5 seconds, or whatever you deem necessary.
    .OnTime Now + TimeValue("00:00:05"), "CoeToBeRunAfterWorkbookOpens"
    End With
    End Sub

    Public Sub "CodToBeRunAfterWorkbookOpens()
    ' Put whatever you need here
    End Sub
    </pre>


  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to run after linked Excel file has updated (Excel 2000)

    As I understood the question, the code needed to be run only once, but AFTER Excel was done doing it's opening business.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Macro to run after linked Excel file has updated (Excel 2000)

    I must have misundestood. I thought it was supposed to run when the linked cell was updated. I still think putting the code in the calculate event is a better way.
    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
  •