• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

ODBC data and pivot update order. Excel 2003

cookmd

New Member
Hello again everyone!


I appreciate all the help on this site, I'm learning a lot! I had a question about the order in which data updates in an excel 2003 spreadsheet.


I have a workbook that uses 2 ODBC queries to update on open (one for each tab.) I also have the pivot table set to update on open. Which one updates first though? I need my ODBC connection to update the data first so that when the pivot updates, it shows the new data.


I know Excel 2003 doesn't update pivot information automatically, even if you have "update on open" checked within the pivot table properties. To get around this, I have borrowed some VBA code from another site. (I can't remember which one now.)

VBA:

[pre]
Code:
Sub RefreshAllPivotTables()

Dim PT As PivotTable
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets

For Each PT In WS.PivotTables
PT.RefreshTable
Next PT

Next WS

End Sub
[/pre]
This should get around the update issue, correct?


Recap:

1. Does ODBC data refresh before or after the pivot table refreshes?

2. Does the above VBA script work to make all pivot tables update in a workbook?


Thanks a million!
 
I'm not sure about #1, but assuming ODBC data gets refresh first, you could actually condense your macro to just:

[pre]
Code:
Sub ShortMacro
ActiveWorkbook.RefreshAll
'That's it! This one line refreshes all queries & PivotTables.
End Sub
[/pre]
 
Back
Top