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)

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 FileReturn 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
HIS


"Peter" wrote in message
...
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 - 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:

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 FileReturn 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
HIS


"Peter" wrote in message
...
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)

Great

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Peter" wrote in message
...
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:

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 FileReturn 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
HIS


"Peter" wrote in message
...
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)