How to change Database location in query

[tviohl]

Hi,
i have a lot of pivot tables in an excel workbook. the datasources are made with ODBC to a local access mdb.
When I deploy the excel file to another PC and create the ODBC DSN I see no data because excel (or query) stores the original path to the MDB in SQL. It does not store the ODBC DSN in SQL!!
How can I change the Paths to the new location of the MDB file?

Thanks in advance.
Tom

 

[SkipVought]

Hi Tom,

Turn on the Macro Recorder.

Selecting the PivotTable, activate the PivotTable Wizard.

Use the BACK key to find the Source Data

Change the data source

Turn off the macro recorder

Inspect and edit the code to execut in a loop as such...

dim ws as worksheet, pt as pivottable
for each ws in worksheetc
  for each pt in ws.pivottables
    pt...... statament to change the data source
  next
next

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SkipVought]

sorry

    Dim ws As Worksheet, pt As PivotTable
    For Each ws In Worksheets
        For Each pt In ws.PivotTables
            pt.SourceData = "Data!" & Worksheets("Data").Cells(1, 1).CurrentRegion.Address(ReferenceStyle:=xlR1C1)
        Next
    Next

just my own SourceData statement

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[tviohl]

Thank you for the answer.
But: the macro recorder is only recording:
activeworkbook.showPivotTableFildList = true

the whole pivot-wizard code was not recorded.

--
Tom

 

[SkipVought]

When you arrow BACK to the source data window, you may need to change the source for the recorder to catch it.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com