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.