Hi,

I am currently working with an Excel file that has many pivot tables using connections to pull data from external Excel sheets. I do not have access to any of these connected external Excel files, but instead have .XLS workbooks that are formatted identically with the same headers, just different data that I want pulled into the pivot table. Here is a picture of the Change Data Source --> Connection Properties tab in Excel showing what I am trying to do.

connection properties.jpg

I am hoping that by simply changing the text string of the file path, the SQL query will remain unchanged and the updated pivot table will reflect the new data source. However, I have not worked with connections or Pivot Tables in VBA very much, and the following attempt I made at accomplishing my task failed miserably. I was trying to fix just one of the pivot tables by clicking it and running this macro.

Sub QueryChange()
Dim OldPath As String, NewPath As String
OldPath = "C:\Production Meeting\SAP Reports Excel\ZME80 - ME5A.xlsx"
NewPath = "C:\Users\blah blah blah\Materials Reports\FPPV Purchase Order Information System.xlsx"

ActiveCell.PivotTable.PivotCache.Connection = Application.Substitute(PivotTable.PivotCache.Connection, OldPath, NewPath)
End Sub
There might be a way to do this by manually changing the Connection File within the Connection Properties, but I get derailed in that effort at the "Select Table" prompt.

Thanks!