I have a large Excel spreadsheet at our company, which contains a lot of pivot chart reports linked to an Access database. It also has graphs linked in turn to these reports.
What I would like to know is, how can I easily update the links to the access database mdb file on ALL reports.
I assume this would need to be done by VBA if possible.
I may have done this wrong, because I can't seem to see how anyone would be able to update a huge number of links to pivot charts should the location or name of the database change!
Also, there are lots of settings under "Table Settings" that I am not sure about.
Could somebody give me some recommendations for the best settings for a large pivot table Excel file.
Data Source options:
-Save data with table layout
-Enable drill to details
-Refresh on open
-Refresh every x minutes
External data options:
If there is a better way to link a pivot table to an Access database that can easily be updated across multiple reports I would like some comments on it please.
One idea I had was to create a "System" odbc DSN connection, and link to that instead of direct to the database. Would this work, and would it need to be created on all machines that use the Excel file identically?
Thanks in advance.