topic Re: Change data for SAS Add-In in Microsoft Integration with SAS

Change data for SAS Add-In

GVeers — Tue, 18 Nov 2014 20:12:09 GMT

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?

Thank you.

Re: Change data for SAS Add-In

SASKiwi — Tue, 18 Nov 2014 22:57:33 GMT

Yes. Open the SAS Data icon under the Excel SAS tab, then select the Browse button to find a different table, either by folder or by SAS server.

Re: Change data for SAS Add-In

GVeers — Wed, 19 Nov 2014 14:15:12 GMT

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?

Re: Change data for SAS Add-In

GVeers — Tue, 25 Nov 2014 13:37:28 GMT

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.

Re: Change data for SAS Add-In

ThomasHirsch — Fri, 05 Dec 2014 07:59:37 GMT

Hi GVeers,

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).

Cheers, Thomas

Re: Change data for SAS Add-In

Bazza669 — Thu, 09 Apr 2015 13:49:44 GMT

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

Re: Change data for SAS Add-In

JonMah — Fri, 15 May 2015 19:50:10 GMT

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.

The underlying data is changed (i.e. data elements are added/changed)
Name of data table changes
Location of data changes
Server changes/updates

We are about to migrate to a different server; so a solution to this issue would be greatly appreciated.

Thanks,
Jon

Re: Change data for SAS Add-In

Guest007 — Thu, 28 May 2015 02:17:15 GMT

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.

Re: Change data for SAS Add-In

Kurt_Bremser — Thu, 28 May 2015 05:54:54 GMT

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.

Re: Change data for SAS Add-In

seanrez — Thu, 28 May 2015 17:04:17 GMT

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.

-Sean

Re: Change data for SAS Add-In

KHaavik — Wed, 08 Nov 2017 03:34:43 GMT

Wow, thanks. That saved my bacon.