topic SAS addin 7.1excel sheet macro to refresh in Microsoft Integration with SAS

SAS addin 7.1excel sheet macro to refresh

varun45 — Thu, 14 Jun 2018 07:22:00 GMT

I am using excel sheet with SAS addin 7.1 . I want to refresh all the data including data sets and pivot tables of the workbook with a macro. I also tried the following macro but the error is "compile error: user-defined type not defined" in the highlighted line

Private Sub Workbook_Open()

Application.COMAddIns.Item("SAS.ExcelAddIn").Connect = True

Dim SAS As SASExcelAddIn

Set SAS = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

SAS.Refresh (SheetName)

end sub()

Note: the declaration of SASexcel addin is the place where the problem persists everytime

Thanks

Varun A

Re: SAS addin 7.1excel sheet macro to refresh

FredrikE — Thu, 14 Jun 2018 08:13:18 GMT

I use this in my excel to refresh everything. Note te delay that makes everything sync, not the nices solution, but it works 🙂

Sub RefreshSasContent()
Dim sas As SASExcelAddIn
Dim pt As PivotTable
Dim ws As Worksheet
Dim slcr As SlicerCache
Dim dblEndTime As Double

' Refresh SAS content
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
sas.Refresh ThisWorkbook

' Add a pause otherwise the update of the pivots doesn't work
dblEndTime = Timer + 1
Do While Timer < dblEndTime
DoEvents
Loop

Refresh all pivots
For Each PivotCache In ActiveWorkbook.PivotCaches
PivotCache.Refresh
Next

' Remove selections
For Each slcr In ActiveWorkbook.SlicerCaches
slcr.ClearManualFilter
Next slcr

End Sub

//Fredrik

Re: SAS addin 7.1excel sheet macro to refresh

fifthand57th — Thu, 14 Jun 2018 18:10:57 GMT

@varun45

Did you add the SAS Add-in reference to your VBA project? From the SAS Add-in help:

Re: SAS addin 7.1excel sheet macro to refresh

varun45 — Mon, 18 Jun 2018 02:53:46 GMT

actually when declaring sas addin in VBA the auto completion is not working for SAS declaration alone and after typing it when executing it is showing like "user defined type not defined"

Re: SAS addin 7.1excel sheet macro to refresh

fifthand57th — Mon, 18 Jun 2018 14:14:40 GMT

@varun45

Yes, autocomplete should not work until the reference is active. Did you follow the screen shot in my previous post and enable the SAS Add-in for Microsoft Office reference before writing any SAS Add-in related VBA?