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 |
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 |