Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing PivotTable Source programmatically
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing PivotTable Source programmatically
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 "Roberto Villa Real" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programmatically getting a Max value from a PivotTable | Excel Programming | |||
Programmatically determine Chart's data source | Excel Programming | |||
Programmatically reading data field values from PivotTable | Excel Programming | |||
Excel VBA - How do I programmatically change source code in another file? | Excel Programming | |||
Changing PivotTable source | Excel Programming |