Change Pivot Source SQL Database

[EvonS]

My IT department for work moved all my source data to a new server so now I need all my pivot tables on multiple sheets to switch over to the new data source. Is there an easy way to do this?

All the instructions I've seen online address changing the source files or cell ranges but not changing a database.

 

[Alex Blakenburg]

If you run the code below it may give you some idea of where to start.

Public Sub Test()
    Dim wb As Excel.Workbook
    Dim pc As PivotCache
    Dim path As String

    Set wb = ActiveWorkbook
    path = wb.path

    For Each pc In wb.PivotCaches
        Debug.Print pc.Connection
    Next
End Sub

 

[EvonS]

If you run the code below it may give you some idea of where to start.

Public Sub Test()
    Dim wb As Excel.Workbook
    Dim pc As PivotCache
    Dim path As String

    Set wb = ActiveWorkbook
    path = wb.path

    For Each pc In wb.PivotCaches
        Debug.Print pc.Connection
    Next
End Sub

Thanks but nothing happened when I ran the code.

 

[Alex Blakenburg]

I only had a connection to an access database, but are you saying the debug.print showed nothing in the vba immediate window ?

 

[EvonS]

I only had a connection to an access database, but are you saying the debug.print showed nothing in the vba immediate window ?

Correct. Nothing popped up. I started to change the pivots manually and for some reason all the sources of the other pivot tables updated automatically. I have no idea why but it worked in my favour. Thanks for the help.

 

[Alex Blakenburg]

Glad it worked out for you.