Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Redirect all Excel pivot tables to a different data source

Posted on 2011-09-03
3
Medium Priority
?
635 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 2000 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.

885 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