Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


ADODB Vba refresh Multible Pivot Tables

ADODB Vba refresh Multible Pivot Tables

ADODB Vba refresh Multible Pivot Tables

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"
    End With

Debug.Print "Connecting.... "     ' for testing
Else                              ' for testing
Debug.Print "Connected"           ' for testing
End If
Set GetConnection = cn

Exit Function

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

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

Set ActiveWorkbook.PivotCaches(1).Recordset = rs

Set rstRecordset = Nothing
Set cn = Nothing

RE: ADODB Vba refresh Multible Pivot Tables

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:
For Each pc In ActiveWorkbook.PivotCaches
Next pc

or loop through sheets and refresh each pivot table:
For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
    Next pt
Next ws
Next pc

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: VBA Visual Basic for Applications (Microsoft) more useful for problems with office applications and vba.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close