BookmarkSubscribeRSS Feed
JRoman
Obsidian | Level 7

Greetings,

 

I'm wondering whether it is possible to leverage the SAS Add-in for Excel to run "raw" SAS code directly from VBA?  For example, I would like to have a VBA macro like the following (which references a hypothetical SubmitCode method):

 

Public Sub foo()
    Dim sas As SASExcelAddIn
    Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
    sas.SubmitCode "%let foo=bar;"
End Sub

I'm not finding any method within the AMO documentation that would perform the role of that hypothetical SubmitCode method.

 

I'm aware that running code in this way is doable using the SAS OLE automation object (http://support.sas.com/documentation/cdl/en/hostwin/69955/HTML/default/viewer.htm#n11qaawfl1x9d6n1p3...), but I would prefer to stick with AMO since I already have other code using AMO and would like to have the same SAS workspace "behind the scenes", in order to have the same work library throughout the process.

 

Thanks for any help,

 

Jake

 

2 REPLIES 2
tomrvincent
Rhodochrosite | Level 12
you could pass the SAS code to a stored process and run it that way.
KatS_SAS
SAS Employee

 

There is no method to run the code directly in the VBA.

However, if you have already defined the SAS Program, executed it in the SAS Add-in and you have results in the workbook,  you could save the workbook, and use the Refresh method to refresh the workbook or a sheet in the workbook that contains the location of the code results. 

 

However, the first suggestion of placing your code in a stored process and executing the store process from the VBA would be a good solution to this question.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Discussion stats
  • 2 replies
  • 1728 views
  • 0 likes
  • 3 in conversation