topic Re: Invoke SAS Stored Process using EXCEL Macro in Developers

Invoke SAS Stored Process using EXCEL Macro

mali — Wed, 03 Jun 2015 17:58:27 GMT

Hi,

I have a stored process that can be run in excel using AMO. I am trying to create a macro in excel that will invoke the stored process directly. My questions are as follows.

1) Since I am using SAS EG , I figured I will need to write code to connect to server for running this Stored process. How do I connect to linux server using EXCEL?

2) How do I find the location of metadata? My stored process is located on the server.

Thanks in Advance

Re: Invoke SAS Stored Process using EXCEL Macro

BrunoSilva — Fri, 05 Jun 2015 18:06:04 GMT

Hello,

I think the paper bellow have exactly what you want, give a check.

Tips and Techniques for Automating the SAS® Add-In for Microsoft Office with Visual Basic for Applications

http://support.sas.com/resources/papers/proceedings11/012-2011.pdf

Sample VBA macro to run a stored process

https://support.sas.com/rnd/datavisualization/BridgeForESRI/V2/stored_process_code.htm

Turn Your SAS® Macros into Microsoft Excel Functions with the SAS® Integrated Object Model and ADO

http://support.sas.com/resources/papers/proceedings14/1598-2014.pdf

Probably you need a few adjustments in order to comply with your SAS version.

Best Regards.

Re: Invoke SAS Stored Process using EXCEL Macro

mali — Wed, 10 Jun 2015 17:16:05 GMT

Hi , I tried to insert the following code in macro editor

Sub TestConnectionToSAS()

Dim sas As SASExcelAddIn

Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

sas.HelloWorld

End Sub

I am getting a complie error: USer-Defined type not defined. I assume this is because connection to API is not working properly. I am unable to go to references tab within macro editor to enable SAS COM add-in.

Am I missing something.Please let me know .

Thanks