[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 57
  • Last Modified:

Excel Macro Pivot Table

I have a spreadsheet with two tabs.
Tab 1 is a Pivot table of Tab 2
Tab 2 is created via a connection through ODBC

I have a macro that request to refresh all but the summary pivot table is being refreshed first so it does not reflect the details which have not yet been refreshed.  The details then are refreshed and accurate but the pivot table is not.

By refreshing again, the pivot table does reflect the details that are now accurate.

Does anyone know how to address this?
0
Joe M
Asked:
Joe M
2 Solutions
 
tomfarrarCommented:
If I understand what you said, it sounds like the macro is "refreshing all" pivot tables before the ODBC is updated.  The order of execution should be reversed.  I believe the "refresh all" command is probably only related to all the pivot tables and not the raw data from the ODBC.  That would be my guess.
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Make sure you use the following line of code before the code which refreshes the pivot table.

Tab2.querytables(1).refresh backgroundquery:=false

See if that resolves your issue.
1
 
Jerry PaladinoCommented:
The behavior your experiencing is typical when"Refresh All" is used in a macro.   Refresh All does update all the connections and the pivot tables but the timing is rarely consistent where the ODBC connection will be executed before the Pivot Table that relies on it.

If your code uses an "ActiveWorkbook.RefreshAll" command, I would suggest that you replace it with the code that Neeraj suggested above to specifically update the ODBC connection and then use the code below after that to update the Pivot Tables.
 Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
      pc.Refresh
    Next pc

Open in new window

1
 
Joe MAuthor Commented:
Thank you everyone for your help
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now