Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need code assistance to update pivot table from an ADO recordset
I have code that works fine in accessing data from a MS Access database via
an ADODB connection and creating a pivot table. My issue is that once created, I am not able to refresh the pivot table without recreating it. In other words, I have the data in a recordset but can't get it into an existing pivot table. Please provide some coding assistance. Code Snippet Sub ADO_PT_Refresh() 'Dimension Variables Dim strDB_Name As String Dim strDB_Location As String Dim strDB_TableName As String Dim strSQL As String Dim objConn As ADODB.Connection Dim objCmd As ADODB.Command Dim objRS As ADODB.Recordset 'Set Variable strDB_Name = Range("db_name").Value strDB_Location = Range("db_location").Value txtAccessFile = strDB_Location + strDB_Name ' Create the SQL & Command strDB_TableName = Range("db_Query").Value strSQL = "Select * FROM " + strDB_TableName + " ;" 'Open Connection Set objConn = New ADODB.Connection objConn.ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & txtAccessFile & ";" & _ "Persist Security Info=False" objConn.Open Set objRS = objConn.Execute(strSQL) ' Create a PivotTable cache Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal) Set objPivotCache.Recordset = objRS '*********************** ' ' Need code to take the Recordset objRS and updated the existing Pivot Table ' ActiveSheet.PivotTables("Test PT") ' '*********************** ' Close the database. objConn.Close Set objConn = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need code assistance to update pivot table from an ADO recordset
Maybe I misunderstood the question, but it looks to me like you are trying to
recreate the pivot cache each time you want to refresh the data. If I understand this correctly, you should not do that, you should simply refresh the pivot table with code similar to: ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh Of course you would need to identify your specific pivot table. Remember that if more than one pivot table is attached to a pivot cache, then all tables will be refreshed when you refresh one. It is a crazy world we live in. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need code assistance to update pivot table from an ADO records
I'm not looking to recreate the pivot table each and everytime, but merely
take the recordset "objRS" and update the pivotcache on ActiveSheet.PivotTables("Test PT"). If I got to the exisitng pivottable and do a right click, the Refresh Data! option is grayed out. I assume that this is because the orginal pivot table was created from ADO recordset, i.e., now disconnected. "TomPl" wrote: Maybe I misunderstood the question, but it looks to me like you are trying to recreate the pivot cache each time you want to refresh the data. If I understand this correctly, you should not do that, you should simply refresh the pivot table with code similar to: ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh Of course you would need to identify your specific pivot table. Remember that if more than one pivot table is attached to a pivot cache, then all tables will be refreshed when you refresh one. It is a crazy world we live in. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need code assistance to update pivot table from an ADO records
Sorry, but I am in over my head on this.
Have you tried to add the code "ActiveSheet.PivotTables("Test PT").PivotCache.Refresh" to your code following "Set objPivotCache.Recordset = objRS" but before closing the database? Tom |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need code assistance to update pivot table from an ADO records
Have tried that and variations of setting cache = recordset, but all give
errors. Thanks for your help "TomPl" wrote: Sorry, but I am in over my head on this. Have you tried to add the code "ActiveSheet.PivotTables("Test PT").PivotCache.Refresh" to your code following "Set objPivotCache.Recordset = objRS" but before closing the database? Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Pivot Table from a DAO recordset | Excel Programming | |||
How to view and update the code behind a pivot table | Excel Programming | |||
reusing a recordset for a pivot-table? | Excel Programming | |||
reusing a recordset for a pivot-table | Excel Programming | |||
VB code to update existing Pivot Table | Excel Programming |