BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
SAS Employee
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

Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

Tatyana_iu
Calcite | Level 5

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.

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
  • 3 replies
  • 2593 views
  • 1 like
  • 4 in conversation