Change Data Source for Pivot Table?

  • Thread starter Thread starter ths78
  • Start date Start date
T

ths78

Thanks for your time and help.

Applications: Excel 2000 and Access 2000

Problem: I'm working with an Excel Pivot Table that is linked to a
Access table for its data source.

How do I change the data source to a table in another access databas
so that I can update my excel pivot table?

thanks again, ths7
 
You could use programming to change the source. For example:

'====================
Sub PivotChangeSource()
With ActiveWorkbook.PivotCaches(1)
.Connection _
= "ODBC;DSN=MS Access Database;" _
& "DBQ=C:\SalesDB.mdb;DriverId=25;" _
& "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
.CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\SalesDB.mdb`.qryInvoices"
End With
End Sub
'==========================
 
Back
Top