Changing PivotTable Source programmatically

[Roberto Villa Real]

I have several pivot tables in a workbook that get data from an Access .mdb
file.

But sometimes I need these pivot tables to obtain data from another Access
file.

Can I programmatically change the Source File of a Pivot Table, without
changing its query? Shall I use the PivotTableWizard? Or is there any
property that contains the Source File path?

 

[Patrick Molloy]

do you read the data using ADO ? If so, then the answer is easy - yes.

when you read the data into a recordset, all you need to do is set the pivot
cache's recordset property to this recordset and refresh the piviot table
To changethe source, all you need is alter the connection string.

do you need a code example?

Sub LoadDataFromAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim SQL As String
Dim i As Long

MyFile = "Risk.mdb"

SQL = "SELECT * FROM BondTable"

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

rst.Open SQL, con, adOpenStatic

Cells.Clear
'headers
For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1).Value = rst.Fields(i).Name
Next
' data
Range("A2").CopyFromRecordset rs

rst.Close
con.Close

Set rst = Nothing
Set con = Nothing

End Sub