Excel pivot tables using Access as the data source

[Guest]

I can successfully create the link between my Excel pivot tables to my Access data base, however Excel will only look for the data in the specific path in which it was created. How can I have my pivot table find and read the Access data even if I move the Access file to another directory

This mainly causes me problems when I distribute my Excel reports. I bundle the Access database with the Excel report but my instruction to the receiver must instruct them to create a specifically named folder under the C drive and store both the Excel pivot table and Access database otherwise the Excel pivot table cannot find the Access database

Does anyone know a way that I can simplify this situation>

 

[Hiran de Silva]

David,

You (or your audience) will need to tell Excel where to find the Access
database somehow.

2 ways I can think of, provided the connection is made with VBA code.
a popup input box for entering the path
OR enter path in a cell
.... the correct path is then picked-up up when the code is run.
Personally I prefer entering the path in a cell.

If you need steps/code samples better ask the Excel Programming NG.

Hiran

 

[BrianB]

I generally find that if both are in the same folder when the Excel lin
is set up (and kept that way) Excel has no problems if the folder nam
is different subsequently