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
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
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!