SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

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

Reply
N/A
Posts: 0

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

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
Super Contributor
Super Contributor
Posts: 3,174

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

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.
SAS Super FREQ
Posts: 271

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

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
Ask a Question
Discussion stats
  • 2 replies
  • 527 views
  • 0 likes
  • 3 in conversation