Change external record source on existing Pivot Table


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\

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What version of Access/Excel?
Karl001Author Commented:
MS 2010
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.
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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.
Karl001Author Commented:
I want to do it by VBA
Over my pay scale.  I missed that in your original question.
Jerry PaladinoCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karl001Author Commented:
Thank you very much for your help.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.