An excel workbook contains a pivot table which is connected to a database (SQL Server) as the data source. The workbook is sometimes used on different PCs which each use different databases as the data source for the pivot table. Tables, fields and content are the same in each database. But the database location, name, ODBC connection, etc. are different.
There seems to be no easy way in Excel to just choose a new data source for an existing pivot table if the file is opened on a PC where the previously used data source is not available. So each time I change the PC, I have to go through the whole process of creating the pivot table in order to connect it to the right database.
I am looking for a script which let's me input or choose the new data source and updates the pivot table accordingly. Don't know how it can be done but I am thinking input of the ODBC name should be sufficient because it connects to the correct database on the particular PC. If it works the way I am thinking of, updating the pivot table's data source should only require me a few seconds.
After the auction I will send the excel workbook that contains the pivot table which I want to use. The project is completed when I receive the Excel workbook containing the VB script and was able to use the script to update the database connection on different PCs.
Currently all databases are SQL Server and functionality with SQL Server is essential. It would be nice if the script would also be able to switch to/from MS Access databases (not a must though). The project should be finished within 3 days after the end of the auction. Will pay a tip if it is finished faster.
There are often such occasions when I need a VB script, so future projects are possible.
Thank you very much in advance!