topic Run SAS code in Excel VBA macro via SAS AMO? in Microsoft Integration with SAS

Run SAS code in Excel VBA macro via SAS AMO?

JRoman — Tue, 30 Oct 2018 21:44:26 GMT

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#n11qaawfl1x9d6n1p3omjbc9ogi0.htm), 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

Re: Run SAS code in Excel VBA macro via SAS AMO?

tomrvincent — Thu, 17 Oct 2019 19:56:48 GMT

you could pass the SAS code to a stored process and run it that way.

Re: Run SAS code in Excel VBA macro via SAS AMO?

KatS_SAS — Mon, 04 Nov 2019 21:24:14 GMT

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.