Redirect all Excel pivot tables to a different data source

I am resubmitting this in hope that I get better advice this time.

I have a workbook with 30 pivot tables, each pulling data from SQL data views.  I need to redirect all 30 tables to a different SQL database.  I know how to do this one pivot table at a time, but is there a way to redirect all of them at once, possibly by using VB?  I need to change the connection strings and the names of the database in the queries.  Thank you for any advice.
ginsburg7Asked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
Here's the basics on changing connections:

You can build on this: http://support.microsoft.com/kb/816562, where you must specify the old and new connection strings.  It SHOULD be fairly straightforward, especially if you have a spreadsheet of old-> new connection strings.  

The tip in on changing locations of where a query or pivot source may reside, and you should be able to build on that.  The key is to change from old to new connection string (disregard the stuff on old folder to new folder, as that's not what I think you're working).

If you build that and need further assistance, I'm glad to help!

Dave
0
 
dlmilleCommented:
This is one of the key commands in the tip, I referenced, that you should be interested in:

            pt.PivotCache.Connection = _
                  Application.Substitute(pt.PivotCache.Connection, _
                  OldPath, NewPath)

Don't forget to change the commandtext property as well...

Here's another example (again, dealing with paths, but the principle is the same):

http:/Q_27238614.html

Cheers,

Dave
0
 
ginsburg7Author Commented:
Thank you, dlmille.

In case others read this, the code from Microsoft has some errors in it.

Change "Dim sh As Worksheet" to "Dim ws As Worksheet" and add "Dim i As Integer" near the bottom, just after "Dim Temp() As String".

It took a while to figure it all out, but I got it working and it does exactly what I need.  Thank you so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.