topic Re: Invoke SAS refresh in SAS add in for office using Excel VBA in Microsoft Integration with SAS

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

deleted_user — Thu, 28 May 2009 15:05:58 GMT

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

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

sbb — Fri, 29 May 2009 17:46:58 GMT

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.

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

CaseySmith — Fri, 14 Aug 2009 20:16:35 GMT

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

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

Tatyana_iu — Fri, 29 Mar 2019 10:40:08 GMT

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.