11-18-2014 03:12 PM
After some searching I could not find an answer to this question.
I have an Excel file with multiple pivots connected to SAS data via SAS Add-In. I would like to change the SAS data set to another SAS data set (in the same directory). Does SAS Add-In allow this?
11-19-2014 09:15 AM
Thanks for the reply.
When I do this, it creates a new pivot table with the new data. Even if I add this new connection, and then change the data source for my pivot table (using Change Data Source), it wipes out all the formatting for the pivot table and is effectively like rebuilding the file. Is there a way I can maintain the pivot table while swapping datasets?
11-25-2014 08:37 AM
If anyone is curious, the way I found to do it is to go into Change Data Source for the pivot table and right click the Connection option. There you can directly modify the path of the data.
04-09-2015 09:49 AM
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).
Dim ws As Worksheet
Dim pvt As PivotTable
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
I've tried code to change the connection string:
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 = _
qy.CommandText = _
For Each pt In ws.PivotTables
pt.PivotCache.Connection = _
On Error Resume Next
pt.PivotCache.CommandText = _
If Err.Number <> 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set rng = pt.TableRange2
pt.TableRange2.Copy Workbooks.Add(xlWorksheet).Worksheets(1) _
ActiveCell.PivotTable.PivotCache.CommandText = _
Set pt = rng.PivotTable
Application.ScreenUpdating = True
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)
StringToArray = Temp
but this failed on
pt.PivotCache.Connection = _
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.
05-28-2015 01:04 PM
This worked for me as well. This is the correct answer! Thanks a lot!! :smileycool:
"If anyone is curious, the way I found to do it is to go into Change Data Source for the pivot table and right click the Connection option. There you can directly modify the path of the data."
First you add the new SAS data table with the "Open SAS Data" button, then you just switch all the pivot tables' connections to the new connection you've created.
12-05-2014 02:59 AM
i face the same problem now and then when i have to change the data source for an existing Excel Report. I'm not sure what 'wipes out all the formatting' means. Is this about the Formats like showing numbers with 2 digits or do you talk about the selected columns und the filters (which are definitely lost when changing the data source).
05-15-2015 03:50 PM
Our group is trying to find a solution to this question as well. We have many Excel workbooks that use tables or pivots that link to SAS data. Whenever one the following events happen, significant (re)work is required to reconnect the exhibit and fix/rebuild the pivot tables.
We are about to migrate to a different server; so a solution to this issue would be greatly appreciated.
05-27-2015 10:17 PM
The Migration Wizard might be a handy tool for your purpose of migration from one server to another server.
There's a migration wizard program in the SAS EG and MS Add-in program folder within /SASHOME in each client installation. No special permission is required to run the MigrationWizard.exe program. The wizard allows you to update the metadata references in your EG projects SAS content reference in MS Office. It can definitely perform some of the metadata changes mentioned in the discussion up to certain degree, unrelated to migration as the utility's name implies.
Hope this helps.
05-28-2015 01:54 AM
Offload the handling of the data into SAS and define a stable interface to the Excel tables. Then you can make sure (eg through keep lists and creating fixed name datasets - data excel_result; set &whatever_it_is_named; run that the tables used by excel always stay the same.
If possible, I'd even do the pivotting in SAS; it's more complicated, but once set up, it is easily automated and documented.