CarenC
asked on
Refresh PivotTable and PivotCache - Excel 2010
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
Set objPivotCache.Recordset = rst
With objPivotCache
.CreatePivotTable TableDestination:=Range("A
End With
With ActiveSheet.PivotTables("T
With .PivotFields("SITE_NAME")
.Orientation = xlRowField
.Position = 1
End With
End With
. . . . more code to define PivotTable
End If
If rst.RecordCount <= 1 Then
MsgBox theMessage("NoData")
Else
' Create Pivot Table
Set objPivotCache = ActiveWorkbook.PivotCaches
Set objPivotCache.Recordset = rst
With objPivotCache
.CreatePivotTable TableDestination:=Range("A
End With
With ActiveSheet.PivotTables("T
With .PivotFields("SITE_NAME")
.Orientation = xlRowField
.Position = 1
End With
End With
. . . . more code to define PivotTable
End If
ASKER
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
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.
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
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.
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("Pivottable
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("Pivottable
ASKER
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=
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
Set objPivotCache.Recordset = rst
ws.PivotTables("TotalContr
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").Pivot
pt.PivotCache.Refresh
Next pt
If I had the ODBC query in a worksheet I would use activesheet.querytables(1)
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=
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
Set objPivotCache.Recordset = rst
ws.PivotTables("TotalContr
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").Pivot
pt.PivotCache.Refresh
Next pt
If I had the ODBC query in a worksheet I would use activesheet.querytables(1)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Set objPivotCache.Recordset = rst
ws.PivotTables("TotalContr
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.
I don't have QueryTables. I used this:
Set objPivotCache = ActiveWorkbook.PivotCaches
Set objPivotCache.Recordset = rst
ws.PivotTables("TotalContr
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.
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.
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.
ASKER
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.
I almost have it working and will confirm tomorrow.
ASKER
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=
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
Set objPivotCache.Recordset = rst
With objPivotCache
.CreatePivotTable TableDestination:=Range("A
End With
‘ Code to create up the pivot; set up the Row Labels, Values, Column Labels and Formulas
End Sub
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=
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
Set objPivotCache.Recordset = rst
With objPivotCache
.CreatePivotTable TableDestination:=Range("A
End With
‘ Code to create up the pivot; set up the Row Labels, Values, Column Labels and Formulas
End Sub
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.
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.
ASKER
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.
Thanks for your help.
ActiveSheet.PivotTables("T