Setting external data location for a pivot table

[Guest]

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.

 

[William]

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

(e-mail address removed)


|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.