?
Solved

Change external record source on existing Pivot Table

Posted on 2014-08-28
8
Medium Priority
?
290 Views
Last Modified: 2014-08-29
Hi,

I have an excel pivot Table base on an access query.

I moved the access database in an other folder.

How can I  change, by VBA, the new path of the external record source of the pivot table?

Example:
Access database was in folder s:\Client\ is now in the folder \\grp1.campus.Mc.com\Client\

Thanks
0
Comment
Question by:Karl001
  • 4
  • 3
8 Comments
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40290384
What version of Access/Excel?
0
 

Author Comment

by:Karl001
ID: 40290406
MS 2010
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40290428
I think if you go to Excel/Data/Connections and select the "Definition" tab you will see under the Connection String a Data Source string.  If you change that string to the new path it should work.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:tomfarrar
ID: 40290434
The other way would be to start over with a blank spreadsheet and using the Excel/Data/GetExternalData from Access and select the query again.  That would work too if you don't have a lot of additional stuff going on in your existing linked spreadsheet.
0
 

Author Comment

by:Karl001
ID: 40290485
I want to do it by VBA
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40290503
Over my pay scale.  I missed that in your original question.
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 2000 total points
ID: 40292543
In the code below Replace the OldPath & NewPath variables with the original path or server name where your database resided, and the new path or server name where your database now resides.   Please check the spelling for the values I changed to the ones in your post above.
Option Explicit

Sub QueryChange()

     Dim ws As Worksheet
     Dim qt As QueryTable
     Dim pt As PivotTable
     Dim OldPath As String
     Dim NewPath As String
     
     'Replace the following paths with the original path or server name
     'where your database resided, and the new path or server name where
     'your database now resides.
    OldPath = "s:\Client\"
    NewPath = "\\grp1.campus.Mc.com\Client\"

     For Each ws In ActiveWorkbook.Sheets
       For Each qt In ws.QueryTables
            qt.Connection = Application.Substitute(qt.Connection, OldPath, NewPath)
            qt.CommandText = Application.Substitute(qt.CommandText, OldPath, NewPath)
'            qt.Refresh
       Next qt

       For Each pt In ws.PivotTables
            pt.PivotCache.Connection = Application.Substitute(pt.PivotCache.Connection, OldPath, NewPath)
            pt.PivotCache.CommandText = Application.Substitute(pt.PivotCache.CommandText, OldPath, NewPath)
'            pt.PivotCache.Refresh
        Next pt
Next ws
End Sub

Open in new window

0
 

Author Closing Comment

by:Karl001
ID: 40292884
Thank you very much for your help.

Carol
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

862 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