BookmarkSubscribeRSS Feed
varun45
Calcite | Level 5

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

4 REPLIES 4
FredrikE
Rhodochrosite | Level 12

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

fifthand57th
SAS Employee

@varun45

 

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

 

2018-06-14_14-09-04.png

varun45
Calcite | Level 5
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"
fifthand57th
SAS Employee

@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?

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
  • 4 replies
  • 4039 views
  • 0 likes
  • 3 in conversation