Changing the Connection link for a Pivot Table in VBA

**BHolligan** · 09-11-2012, 10:48 AM

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!

**JosephP** · 09-11-2012, 11:04 AM

did you try refreshing the cache afterwards?
I reckon you may be using an ODBC connection there in which case if you have multiple pivot tables built from the same connection you cannot repoint it without changing it to an OLEDB connection instead if I remember right

**BHolligan** · 09-11-2012, 11:08 AM

Yeah I ran this in the immediate window.

? activesheet.pivottables(1).pivotcache.connection
ODBC;DSN=Excel Files;DBQ=C:\Production Meeting\SAP Reports Excel\ZME80 - ME5A.xlsx;DefaultDir=C:\Production Meeting\SAP Reports Excel;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

I don't fully understand the differences in connection types, but I'll look into what you said. Also, any idea how to best reference different Pivot Tables? Obviously .pivottables(#) works, but there are roughly 20 in this workbook, and that seems like a confusing way to go about it.

**JosephP** · 09-11-2012, 11:17 AM

you can use a variation of this (it's for changing the commandtext of a pivot)

Sub ChangePivot()
   Dim pc As PivotCache
   Set pc = ActiveSheet.PivotTables(1).PivotCache
   ChangePC_SQL pc, "SELECT `Sheet1$`.F1, `Sheet1$`.F2, `Sheet1$`.F3, `Sheet1$`.F4 FROM `C:\Book1`.`Sheet1$` `Sheet1$`  WHERE `Sheet1$`.F1 = 2"
End Sub
Sub ChangePC_SQL(pc As PivotCache, strSQL As String)
   Dim blnODBC As Boolean
   With pc
      If .QueryType = xlODBCQuery Then
         blnODBC = True
         .Connection = Replace$(.Connection, "ODBC;DSN", "OLEDB;DSN", 1, 1, vbTextCompare)
      End If
      If StrComp(.CommandText, strSQL, vbTextCompare) <> 0 Then
         .CommandText = strSQL
      End If
      
      If blnODBC = True Then
         .Connection = Replace(.Connection, "OLEDB;DSN", "ODBC;DSN", 1, 1, vbTextCompare)
      End If
      .Refresh
   End With
End Sub

which will change the connection to OLEDB at the same time you change the commandtext (you can just leave the commandtext the same)

you can refer to pivot tables by name as well as index, or just loop through each one on each sheet using a for each... next loop