Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    87
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Order of events for opening a workbook

    Is there a reference that shows what happens in what order when opening an Excel workbook? Lots of Googleing and Binging leads only to a page from Pearson Consulting that has been replaced with a page that doesn't have the information.

    In particular I need to know whether external data sources complete their refresh before the Workbook events start.

    In case there is a better solution, here is what I am up to:

    I have a workbook that shows a bunch of pivot tables. These are based on data drawn from a web source, which is set to refresh automatically on open in the Data Connection properties. The data is then filtered by date using an advanced filter executed in a macro. The pivot table data source is the filtered table.

    What I want is for the workbook to first pull the data, then do the advanced filter copy, and then update the pivot table caches or reports. I've got code that does the latter two tasks just fine.

    I think that I could remove the automatic data refresh and put the whole operation into the workbook open event macro, but I would like to avoid that. If the events happen in the order I need I can avoid it.

    Any ideas?

    Thanks.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Here are some references.
    http://www.ozgrid.com/Excel/free-tra...a1lesson15.htm
    http://pixcels.nl/events-in-workbooks/

    Here is a Chip Pearson one (and may be the one you indicated), but it may be useful to others:http://www.cpearson.com/excel/Events.aspx

    You may need to add
    DoEvents

    in your code to ensure that some tasks complete before the next line is called...

    Steve

  4. The Following User Says Thank You to sdckapr For This Useful Post:

    jweissmn1 (2014-07-31)

  5. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,243
    Thanks
    202
    Thanked 796 Times in 729 Posts
    You got my curiosity up so I ran a little test:
    ExcelWorkbookOpenEventsOrder.JPG

    Note: The code is in the ThisWorkbook Object.

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #4
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    87
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Yes, that's the one that replaced the one that showed event order. It is illuminating about the whole topic, and I hope it helps others.

    And thanks for the DoEvents hint. That may be the key.

    Also, it seems that even for connections that are based on web queries all I need is a simple refresh of the connection, without having to refresh the query table. That would be good.

Posting Permissions

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