Hi

This may help a little


Sub AmendPivotLocation()
Dim pt As PivotTable
Dim pc As PivotCache
Dim oldpath As String
Dim newpath As String
Dim ws As Worksheet
Dim wb As Workbook

Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next

'Amend paths as necessary where Access database is stored
oldpath = "C:\MyFolder\FolderA\Work"
newpath = "C:\Work\Whatever"

Set wb = ThisWorkbook
For Each ws In wb.Worksheets
For Each pt In ws.PivotTables
pt.EnableWizard = True
Set pc = pt.PivotCache
pc.EnableRefresh = False
pc.Connection = Replace(pc.Connection, oldpath, newpath)
pc.EnableRefresh = True
pc.CommandText = Replace(pc.CommandText, oldpath, newpath)
Next pt
Next ws
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

--

Regards

William

XL2003




"IMT" wrote in message
...
|I want to create a spreadsheet that contaiins multiple pivot tables for a
| single access database. As far as I see, Excel hardcodes the DSN
information
| for the ODBC connection. Thus if I give this speadsheet and access
database
| to someone else, they must put the access database in exactly the same
| location (i.e. drive letter and subdirectory).
|
| Normally, you would make a call for external data to the DSN which
contains
| the pointer to the location of the database. Thus you can move the
database
| anywhere you want as long as you relfect those changes in one single
| location, the DSN. Excel seems t hardcode the DSN name as well as the path
to
| the database thus not allowing you to change it location.
|
| As a work around I had a thought to create queries to use for each of the
| pivot tables. I then tried creating the pivot tables based on a saved
| queries. I figured then you can edit the individual query and change the
| drive letters for all the info in the query (i.e. DSN, default location,
etc)
| since this all seems to be saved within the query. This would be labor
| intensive but thought to be a workaround. However, when I saved the
| spreadsheet with the pivot table. Disconnected the original mapped drive,
| editited the query to reflect the new drive letter and tried to open up
and
| refresh the data, I got the error message telling me that it couldn't find
| the ODBC datasource at the location of the original drive letter so it
hard
| coded again in there somewhere.
|
| Anyone know of a workaround. Don't know if somethig could be done in VBA.