Change external record source on existing Pivot Table

Posted on 2014-08-28
Last Modified: 2014-08-29

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?

Access database was in folder s:\Client\ is now in the folder \\\Client\

Question by:Karl001
    LVL 7

    Expert Comment

    What version of Access/Excel?

    Author Comment

    MS 2010
    LVL 7

    Expert Comment

    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.
    LVL 7

    Expert Comment

    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.

    Author Comment

    I want to do it by VBA
    LVL 7

    Expert Comment

    Over my pay scale.  I missed that in your original question.
    LVL 16

    Accepted Solution

    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 = "\\\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


    Author Closing Comment

    Thank you very much for your help.


    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    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,…
    A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
    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 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.

    755 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

    18 Experts available now in Live!

    Get 1:1 Help Now