I am also trying to change a data source, from one Mart map to another. I have a workbook with multiple sheets, and nearly 70 connections to 3 Maps. I'm trying to change the maps used, from "SASApp_Prod:Map1" to "SASApp_Development:Map2" If I try the 'Change Data Source' method all I see under Connection Properties/Definition is, for example, Connection Name: Connection56 and Connection Type: ADO Recordset, all other fields are greyed out. I have tried multiple VBA connection properties to see the values of various VBA properties, and the only ones I seem to be able to see are Name, Type (always 1),Description (always blank) and InModel (always False). Sub FindPivotConnections() Dim ws As Worksheet Dim pvt As PivotTable Dim i Dim conn As WorkbookConnection Dim strConn As String Dim arrConn As Variant '************************************************* 'Code between asterisk lines for information only 'For i = 1 To ThisWorkbook.Connections.Count For i = 55 To 56 Set conn = ThisWorkbook.Connections(i) MsgBox "Conn.name " & i & "= " & conn.Name MsgBox "Conn.Type " & i & "= " & conn.Type MsgBox "Conn.Description " & i & "= " & conn.Description MsgBox "Conn.InModel " & i & "= " & conn.InModel Next i End Sub I've tried code to change the connection string: Sub QueryChange() Dim sh As Worksheet, qy As QueryTable Dim pt As PivotTable, pc As PivotCache Dim OldPath As String, NewPath As String Dim rng As Range '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 = "SASApp_Prod" NewPath = "SASApp_Development" For Each ws In ActiveWorkbook.Sheets For Each qy In ws.QueryTables qy.Connection = _ Application.Substitute(qy.Connection, _ OldPath, NewPath) qy.CommandText = _ StringToArray(Application.Substitute(qy.CommandText, _ OldPath, NewPath)) qy.Refresh Next qy For Each pt In ws.PivotTables pt.PivotCache.Connection = _ Application.Substitute(pt.PivotCache.Connection, _ OldPath, NewPath) On Error Resume Next pt.PivotCache.CommandText = _ StringToArray(Application.Substitute(pt.PivotCache.CommandText, _ OldPath, NewPath)) If Err.Number <> 0 Then Err.Clear On Error GoTo 0 Application.ScreenUpdating = False Set rng = pt.TableRange2 pt.TableRange2.Copy Workbooks.Add(xlWorksheet).Worksheets(1) _ .Range("A1") ActiveCell.PivotTable.PivotCache.CommandText = _ StringToArray(Application.Substitute(pt.PivotCache.CommandText, _ OldPath, NewPath)) ActiveCell.PivotTable.TableRange2.Copy pt.TableRange2 ActiveWorkbook.Close False Set pt = rng.PivotTable Application.ScreenUpdating = True End If pt.PivotCache.Refresh Next pt Next ws End Sub Function StringToArray(Query As String) As Variant Const StrLen = 127 Dim NumElems As Integer Dim Temp() As String NumElems = (Len(Query) / StrLen) + 1 ReDim Temp(1 To NumElems) As String For i = 1 To NumElems Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen) Next i StringToArray = Temp End Function but this failed on pt.PivotCache.Connection = _ Application.Substitute(pt.PivotCache.Connection, _ OldPath, NewPath) I can't even see what PivotCache.Connection is. I'm using Excel 2013, SAS Add-In 5.1 and my Mart maps are on a metadata server. Any ideas? Thanks Barry
... View more
