Pivot and changing External data source

[Peter]

I have in Excel (2003) a Pivot table pointing at a Access database.

Now I have moved the Access database to a new location and want to point the
Pivot table to that path. When I refresh my Pivot table I am warned that the
original file does no longer exist and when I Click "OK" the Login dialogue
appear where I can select a new database. If I select the new location of the
database and finish this dialogue I still get an error message that Excel can
not find the mdb-file in the original location.

I have studied other related topics on this site and it is suggested that
the following VBA bit is executed:

Sub ChangeConn()

Dim qt As QueryTable
Dim Wsh As Worksheet
Dim OldLoc As String, OldPath As String
Dim NewLoc As String, NewPath As String
Dim LastSlash As Long
Const Ext As String = ".mdb"

OldLoc = "C:\TempX\Finance follow-up"
NewLoc = "C:\Access\Production\Finance follow-up"

LastSlash = InStrRev(OldLoc, "\", , vbTextCompare)
OldPath = Left(OldLoc, LastSlash - 1)

LastSlash = InStrRev(NewLoc, "\", , vbTextCompare)
NewPath = Left(NewLoc, LastSlash - 1)

For Each Wsh In ThisWorkbook.Worksheets
For Each qt In Wsh.QueryTables
qt.Connection = Replace(qt.Connection, OldLoc & Ext, NewLoc &
Ext)
qt.CommandText = Replace(qt.CommandText, OldLoc, NewLoc)
qt.Connection = Replace(qt.Connection, OldPath, NewPath)
qt.Refresh
Next qt
Next Wsh

End Sub

I have tried this as well - unfortunately the result is the same. What can I
do to solve this problem - I would not like to have to re-design Pivot tables
in my Excel spreadsheet.

I appreciate any help on this issue.

Best regards
Peter
(Copenhagen)

 

[Nick Hodge]

Peter

Instead of refreshing, invoke the pivot table wizard while in your pivot
table and press the 'back' button. Press Get Data... and it will likely
error, navigate to the file via the database dialog and it should then error
but ask if you want to continue in MSQuery. Answer yes and then navigate to
the file/table/query from there, select File>Return data to MS Excel (In
MSQuery) and finish the pivot table dialog, save your file and all should be
well

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Peter]

Nick - thanks a lot this worked.

I also noticed that I could obtain the same result while in MSQuery by
editing directly in the SQL statement.

Once again thank you for your time.

Best regards
Peter


"Nick Hodge" skrev:

 

[Nick Hodge]

Great

Yes editing the sql directly in MSQuery will work too...whatever, glad it
worked out

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)