Solved

Refresh PivotTable and PivotCache - Excel 2010

Posted on 2013-11-11
11
1,646 Views
Last Modified: 2013-11-18
I created a PivotTable from a query using the following.  The SQL pulls a start and end date that the user enters on a worksheet.  How can I refresh the PivotTable/PivotCache instead of creating it (PivotCaches.Add)?

If rst.RecordCount <= 1 Then
        MsgBox theMessage("NoData")
    Else
        ' Create Pivot Table
        Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        Set objPivotCache.Recordset = rst
        With objPivotCache
            .CreatePivotTable TableDestination:=Range("A5"), TableName:="TotalContractorResults"
        End With

        With ActiveSheet.PivotTables("TotalContractorResults")
            With .PivotFields("SITE_NAME")
                .Orientation = xlRowField
                .Position = 1
            End With
        End With

       . . . . more code to define PivotTable


    End If
0
Comment
Question by:CarenC
  • 6
  • 5
11 Comments
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39640067
Use

ActiveSheet.PivotTables("TotalContractorResults").PivotCache.Refresh
0
 

Author Comment

by:CarenC
ID: 39641536
I put that line in different places and always get the error "Application-defined or object-defined error".  I don't have a good understand of pivotcaches yet so I'm just guessing.

I think I'd have to change this line but I don't know what it should be.  I tried commenting it out and adding your line but get the same error.
      Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)

I know the query works because I have some code to write the query and then I run it in SQL Mgmt Studio and get the results I want but the pivot table doesn't update.
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39642340
Perhaps the table name is wrong or the sheet is not active.
Try use the macro recorder to get the correct name of the pivottable
Start the recorder at the Developer tab, and update the pivottable.
Stop the recorder, and look in the VBA module where the macro is recorded.
Test the recorded macro.
Copy the line to where you need it.

The sheet does not have to be active to update the pivotcache if the sheet is defined in a variable, like this.

    Dim ws As Worksheet
    Set ws = Worksheets("Sheetname")

Then the pivotcache can be updated using the sheet reference.

    ws.PivotTables("Pivottablename").PivotCache.Refresh
0
 

Author Comment

by:CarenC
ID: 39642937
Here's my latest attempt that doesn't work and using your suggested line.  Instead of what I had in my original post I used PivotCaches.Create.

Public Sub RunQuery(ByVal sSQL As String)
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stConn As String
    Dim ws As Worksheet
    Dim objPivotCache As PivotCache
   
    Set cnt = New ADODB.Connection
    stConn = "PROVIDER=SQLOLEDB;SERVER=MyServerName;MyDatabaseName;UID=MyUID;PWD=MyPWD"
   
    cnt.ConnectionString = sSQL
   
    Set ws = Worksheets("Report")
    ws.Select
   
    With cnt
        .CursorLocation = adUseClient
        .Open stConn        
        Set rst = .Execute(sSQL)
    End With

'    If No Data was returned by query alert the user
    If rst.RecordCount <= 1 Then
        MsgBox "No Data"
    Else
   
        Set objPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
        Set objPivotCache.Recordset = rst
        ws.PivotTables("TotalContractorResults").PivotCache.Refresh           ' this line throws the error
       
        Range("A1").Select
       
    End If
    cnt.Close
   
End Sub


Whereever I put that line you supplied I get the error "Application-defined or object-defined error".

When I record a macro all I get is "ActiveWorkbook.RefreshAll."

How does the pivot table recognize that there's new data cached from the new query?  Is that what PivotCaches.Create does?

I ran this code and get the same error.  In the watch window pt.value = "TotalContractorResults" so I know I have the right name.
     For Each pt In Worksheets("Report").PivotTables
            pt.PivotCache.Refresh
        Next pt

If I had the ODBC query in a worksheet I would use activesheet.querytables(1).backgroundquery = false. Do I need to do the same for cached query data . . . if so how?
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 39643050
When you record the macro select a cell in the pivottable.
Right-click and select Update on the menu, just to update this one pivot, and get the Refresh command for that.

I use the same syntax for querytables as you mention
Worksheets("Sheetname").QueryTables(1).Refresh (False)
or
Worksheets("Sheetname").QueryTables(1).Refresh BackgroundQuery:=False
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:CarenC
ID: 39643157
I did select a cell in the pivottable.  I'm not seeing what you're suggesting . . . There's no "update" when I right click (Excel 2010).  I went to the PivotTable ribbon and selected Refresh, the only option there was to RefreshAll.  Refresh was grayed out.

I don't have QueryTables.  I used this:
      Set objPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
      Set objPivotCache.Recordset = rst
      ws.PivotTables("TotalContractorResults").PivotCache.Refresh           ' this line throws the error

I can't use QueryTables because the start and end date is a parameter and I get an error that I think is a known bug but I can't find the references I read about this.
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39646118
I am using Excel 2007, a non english version, so I am not sure it is Update in the right-click menu (for me it is), but could be it is Refresh.
Select the menu item in the right-click menu to Update/Refresh the Pivot table.
If that is not possible, then it is also not possible to refresh from VBA.

You can use worksheet cells as parameter links to a query table, also dates.
1. Select the table, right click, select Table and Edit.
2. Set the criteria you want, (to get the format for the fields).
3. At some point you can desire to return the table, or edit in MSQuery, select MSQuery.
4. Then change the value in the criteria to [Parameter].
5. Use a meaningful name (don't use a field name) for Parameter in the [ ] brackets.
6. Hit Enter, and you will be asked for the criteria value.
7. Return the table to Excel (Menu Files).
8. Select the table, right click, select Parameters, attach the parameter to a cell, mark for update when cell change.

Then change the cell value, and the table will update.
You can use more than one parameter.
The criteria could be >=#01-01-2013# (dd-mm-yyyy format, use what you have).
That can be replaced by >=[StartDate] in MSQuery.
0
 

Author Comment

by:CarenC
ID: 39649444
I'm using 2010 and Refresh for the pivot table is grayed out.  What you suggest for getting the parameters from a cell does not work.  It's a documented issue which is why I went with VBA.  I will switch to writing the query table to a tab and have the pivot table based on that.  

I almost have it working and will confirm tomorrow.
0
 

Author Closing Comment

by:CarenC
ID: 39651833
This answer didn't quite solve the problem but with the mention of querytable I eventually realized the I had to have a query table in a tab rather than pivot cache.  I was able to arrive at a solution based on this realization. It may have been an English as second language issue as the comments said s/he was using a non English version of excel.

The query has to be done with VBA because it uses parameters the user enters, I took the approach of using VBA to run the query. First I delete the old querytable.  Then run the query and convert it to a table.  The next step is to delete the previous pivot table and create a new one based on the table just created.  If there's a better VBA way I'm certainly open to suggestions.  My final code is below.

If you try to run a query using Data->From Other Sources->From Microsoft Query and have parameters, you will have error messages.  This is a known issue which is described here.

Here's the final code.

Public Sub RunQuery(ByVal sSQL As String)
    ' If this doesn't work check Tools -> References and be sure that Microsoft ActiveX Data Objects 2.8 Library is selected
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stConn As String
    Dim ws As Worksheet
    Dim objPivotCache As PivotCache
   
    Set cnt = New ADODB.Connection
    stConn = "PROVIDER=SQLOLEDB;SERVER=MyServer;DATABASE=MyDatabase;UID=MyUID;PWD=MyPWD"
   
    cnt.ConnectionString = sSQL
   
    Set ws = Worksheets("Total Contractor Report")
    ws.Visible = xlSheetVisible
    ws.Select
   
    ' Delete the PivotTable that's already there
    Columns("A:F").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
   
    ' Open the connection and run the query
    With cnt
        .CursorLocation = adUseClient
        .Open stConn
        Set rst = .Execute(sSQL)
    End With
   
    If rst.RecordCount <= 1 Then
        ' If No Data was returned by query alert the user
        MsgBox "No records were found with the date range selected. Please enter a new start and end date."
    Else
        ' Create Pivot Table
        Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        Set objPivotCache.Recordset = rst
        With objPivotCache
            .CreatePivotTable TableDestination:=Range("A5"), TableName:="TotalContractorResults"
        End With

        ‘  Code to create up the pivot; set up the Row Labels, Values, Column Labels and Formulas
   
End Sub
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39654726
I don't understand the problem with SQL queries and parameters linked to cells, I use that frequently.
Make a SQL query on a sheet, set it up with parameters as I have described, and then make the pivot table based on the query result returned to the sheet.
Refresh the query when parameters change, and then refresh the pivot table.
VBA is not required to do that.
Or make a worksheet change event refresh the query when the parameter cells change, and then refresh the pivot table, to make it automatic.
I don't see a reason to rebuild the pivot table when the query result change, refresh should be sufficient.

But good you found a solution, that is what is important.
0
 

Author Comment

by:CarenC
ID: 39656329
Regarding your comment about makng an sQL query on a sheet . . . it doesn't work for the query I'm doing.  As I said . . .If you try to run a query using Data->From Other Sources->From Microsoft Query and have parameters, you will have error messages.  This is a known issue which is described here.  I forgot to mention that it happens when you have a query that can't be displayed graphically.  Simpler queries with parameters work okay as I use that all the time.

Thanks for your help.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now