Solved

Redirect all Excel pivot tables to a different data source

Posted on 2011-09-03
3
573 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:ginsburg7
  • 2
3 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 36479394
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
 
LVL 42

Expert Comment

by:dlmille
ID: 36479399
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
 

Author Closing Comment

by:ginsburg7
ID: 36481217
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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question