Invoke SAS refresh in SAS add in for office using Excel VBA

[deleted_user]

Hi,

Is there a way to refresh multiple data sets mapped to the excel spread sheet (using sas add in) through Excel VBA?

What am looking at is on opening the excel spread sheet the vba code will automatically refresh all data sets using the refresh multiple option in SAS addin.

Please share your insights on this.

Thanks

[sbb]

Please clarify (for me at least) your requirement - you want to launch a SAS execution from Excel VBA code (executing SAS on the local machine), and with a SAS program, you want to "refresh" or replace some existing SAS datasets using the Excel workbook contents as input?

Scott Barry
SBBWorks, Inc.

[CaseySmith]

Yes, there are two ways:

1) Via UI:
Right-click the data view, select Properties, then check "Refresh on file open" on the Execution tab

2) via VBA using AMO's basic scripting model:

Sub RefreshWorkbook()

'get the addin
Dim ComAddin As ComAddin
Dim SasAddinObj As SASAddin

Set ComAddin = Application.COMAddIns("SAS.OfficeAddin.Loader.ConnectProxy")
Set SasAddinObj = ComAddin.Object
Set ComAddin = Nothing

' refreshes all the AMO content (tasks, reports, data views, etc. in the workbook)
SasAddinObj.Refresh (Workbook)

End Sub

[Tatyana_iu]

Second way don't work in my file.

Compile error: User-defined type not defined.

I've chenged "SasAddinObj As SASAddin"  to "Dim SasAddinObj As SASExcelAddIn".

I have error: Run-time error '9':

Subscript out of range.

Please help me to refresh Pivot table with VBA.