ADODB Vba refresh Multible Pivot Tables

[Pillepop]

Hi all,

I’m trying to create a file with different Pivot Tables, all Pivots have different Data sources (created via ADODB and VBA).
So far I have successfully created one Pivot table and I’m able to be flexible refresh it by assigning a new recordset and refresh the the whole thing.
Unfortunately, I have no idea how I can do this with various PivotTables; all of them with different Data sources (not sharing the same PivotCache)

Any Idea would be much appreciated


Code to Connect to DB (ADODB):
------------------------------------------------------------------
Public Function GetConnection() As ADODB.Connection
Dim cn As ADODB.Connection
On Error GoTo ErrorHandler

If cn Is Nothing Then

Set cn = New ADODB.Connection
With cn
.Provider = "OraOleDb.Oracle"
.Properties("Data Source").Value = "xxx"
.Properties("User ID").Value = "xxx"
.Properties("Password").Value = "xxx"
.Open
End With


Debug.Print "Connecting.... " ' for testing
Else ' for testing
Debug.Print "Connected" ' for testing
End If

Set GetConnection = cn

ErrorExit:
Exit Function

ErrorHandler:
MsgBox "No Connection to Server possible"
Resume ErrorExit

End Function


Code to refresh PivotTable:
------------------------------------------------------------------
Public Sub Button2_Click()
Dim rs As ADODB.Recordset
Dim Sql As String


Call GetConnection

Sql = "select * from Names where ID =" & Worksheets("Pivot").Range("B2").Value


Set rs = New ADODB.Recordset
Debug.Print Sql
With rs
.ActiveConnection = GetConnection
.Source = Sql
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With

'avoid update pivot with no data
If rs.RecordCount < 1 Then
MsgBox "No DATA"
GoTo STEPOUT
End If


Set ActiveWorkbook.PivotCaches(1).Recordset = rs
ActiveSheet.PivotTables("PT1").PivotCache.Refresh

STEPOUT:
GetConnection.Close
Set rstRecordset = Nothing
Set cn = Nothing

 

[combo]

So where the problem is? A workbook can contain several pivot caches. Each pivot cache can have several pivot tables, in one or more woksheets. When you refresh a pivot cache all tables based on it are refreshed.
You can loop through pivot caches in a workbook and refresh them:
[tt]For Each pc In ActiveWorkbook.PivotCaches
pc.Refresh
Next pc[/tt]
or loop through sheets and refresh each pivot table:
[tt]For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.Refresh
Next pt
Next ws
Next pc[/tt]
You can also name your pivot tables and refresh by name (as at the end of your post). Generally there is no need to re-create pivot cache for existing pivot table, the definition (and data if set so) stay saved with the workbook.
You may find forum707 more useful for problems with office applications and vba.

combo