Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: External Data (MSQuery) AND Using and Auto_Open Macro

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Poppy @ Alcoa
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is really two problems: A Pivot Table won't refresh, and an Auto_Open Macro won't run.

    I have a worksheet linked to a table in MS Access. I created it by "Get External Data" - "MSAccess", etc., and ultimately selecting "Update on Open" so that each time the worksheet is opened, the data is refreshed. THIS PART WORKS.

    However, I created a Pivot Table based on that worksheet, and although I have checked the box in the Pivot Table "OPTIONS" to "Refresh on Open", it does not refresh. As a work-around, I tried to create an Auto_Open Macro that has just a couple of lines of code-- to select the worksheet, go to a legitimate cell, and refresh the Pivot Table. The macro doesn't appear to be running when the workbook is opened. But if I run the macro after the worksheet is open, the macro commands run as intended. ANY HELP WOULD BE APPRECIATED. Thank you.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Your remove the refresh on open on both the query and the pivottabel and then use the auto_open macro to refresh both.
    I thing the problem is that your pivottable refreshes before your query, so you will never have new results in the pivottable.

    Sub auto_open()
    Selection.QueryTable.Refresh BackgroundQuery:=False
    ActiveSheet.PivotTables("Pivottabel1").RefreshTable
    End Sub

    regards Tommy

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I agree with the post above, pivots are fast ODBC is slow i always tell guys getthe data then minipulte it NOT before or both together


    i would have two scipts ...


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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