Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel Macro Pivot Table

Posted on 2016-07-21
4
52 Views
Last Modified: 2016-07-22
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
Comment
Question by:Joe M
4 Comments
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41723474
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
 
LVL 30

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41723509
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
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 250 total points
ID: 41723786
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
 

Author Closing Comment

by:Joe M
ID: 41725127
Thank you everyone for your help
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
All of the resources available today make learning a new digital media easier than ever-- if you know where to begin. This is a clear, simple guide to a few of the basic digital art mediums and how to begin learning them on your own.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question