BookmarkSubscribeRSS Feed
GVeers
Calcite | Level 5

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.

10 REPLIES 10
SASKiwi
PROC Star

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.

GVeers
Calcite | Level 5

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?

GVeers
Calcite | Level 5

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.

Bazza669
Calcite | Level 5

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

seanrez
Fluorite | Level 6

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

ThomasHirsch
Fluorite | Level 6

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

JonMah
Calcite | Level 5

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

Guest007
Calcite | Level 5

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.

KHaavik
Obsidian | Level 7

Wow, thanks. That saved my bacon.

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 10 replies
  • 10567 views
  • 3 likes
  • 9 in conversation