INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

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

Jobs

ADODB Vba refresh Multible Pivot Tables

ADODB Vba refresh Multible Pivot Tables

(OP)
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
 

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
    pc.Refresh
Next pc

or loop through sheets and refresh each pivot table:
For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.PivotCache.Refresh
    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.

combo

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!

Resources

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