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
