Changing actual data source of pivot table

R

rheath

New Member
Joined
May 21, 2008
Messages
2
I have a pivot table that's using an Access database as its source. I've changed the location of the database so now the pivot table says it can't find the data. I'd like to change the pivot table to go after the new database, but it almost seems it can't be done.

Every time I try to find an answer to this, either in Excel help or in Google, I only find how to change which FIELDS I retrieve, not which DATABASE the data is retrieved from. It almost seems to be "hard coded" once the pivot table has been created. I have a number of pivot tables that need this change and I'd hate to have to create them all again.

Anyone know what I can do? Thanks!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
http://www.mrexcel.com/forum/showthread.php?t=315657&highlight=immediate+window

To reiterate the instructions to get to the immediate window in the VBE:

Select/Activate the Pivot Table
Press Alt + F11
Hopefully you will see a little window that says "Immediate" in the title bar
If you don't, press Control + G

Fazza's instruction are basically to first get the connection information (here, you have the question mark at the beginning of the line) then to enter a new connection property (here, you have the equal sign at the end of the line and the new connection information)

HTH

Possibly, the non-VBA solution is to create a new DNS (Data Source Name) that you can select using get external data from the pivot table wizard - if you create a new DNS you can re-use it. The connection information is saved in the workbook, though (as you've now discovered). Fazza's solution looks great to me - I'd try that first.
 
Upvote 0
(y) Glad you worked it out, guys.

A refinement might be to use a cell on a worksheet to store the connection string. Have a worksheet change event so that when the cell entry changes the code sets the new string to the connection. Untested. I guess it really should have some error checking too. And some backup of the previous connection - maybe easily done to a defined name. Maybe too a msgbox confirmation, such as, "Are you sure?" Regards, Fazza

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Target.Cells(1).Address = "$A$1" Then
    Application.EnableEvents = False
    With ActiveSheet.PivotTables(1).PivotCache
      .Connection = Range("A1").Value2
      .MissingItemsLimit = xlMissingItemsNone
      .Refresh
    End With
    Application.EnableEvents = True
  End If
    
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Pivot table - source data
Replies
0
Views
464
an_eta
A
C
  • Question
Pivot table - sum a number of group
Replies
0
Views
229
Cferron
C
T
  • Question
Pivot Table - Conditional Formatting (or selecting) from a Data Table
Replies
0
Views
559
Timbucktoo
T
S
  • Solved
"There's already data" error on changing slicer selections - how to find source?
Replies
2
Views
466
SueKi
S
T
  • Solved
Create pivot table using VBA?
Replies
4
Views
512
TheWennerWoman
T

Forum statistics

Threads
1,214,932
Messages
6,122,331
Members
449,077
Latest member
jmsotelo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top