Solved

Redirect all Excel pivot tables to a different data source

Posted on 2011-09-03
3
545 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 41

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 41

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now