Solved

Redirect all Excel pivot tables to a different data source

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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.

758 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

20 Experts available now in Live!

Get 1:1 Help Now