Pivottable database connections

[Derek]

We have a large number of pivottables, all within the one
excel workbook using an ODBC connection to a SQL Server
database. The SQL Server database has some complex
queries in it to derive the data for the reports.

They are essentially used as a reporting tool from the
database. There are about 50 people using these reports
on a daily basis.

Until now we have simply been making changes to the
reports on the live database as it has mostly been new
developments or minor changes. However we are about to
start making major changes which will take several days
in development before moving to the production copy.

What is the most effcient way of having a development SQL
Server database and development Excel Spreadsheet and
then moving them into production. My main problem is the
connection between the pivottables and database as it
takes forever to change all these connections.

Is there a quick way to change the connections? If not
what is the best workaround?

Thanks
Derek

 

[Guest]

something like this should work - or at least give yousome ideas..

for each sh in thisworkbook.worksheet
for each qt in sh.querytable
msgbox qt.connectio
qt.connection = "Your new connection string
nex
next