Yes. Look for any entry around Integration Technologies and VBA or C#. I personally would not use VBA (dead tech but still works). I automate using C# outside of Excel and use Excel as a datasource and then launch SAS code from there. I have loads of blog posts on the use. Feel free to search and look at my blog on these techniques:
http://savian.blogspot.com/
Essentially, you have this (in C#):
private Workspace CreateWorkspace() { Workspace sasWorkspace; try { var objFactory = new ObjectFactoryClass(); var serverDef = new ServerDefClass(); // ************************************************ // Configure the SAS server information. // ************************************************ serverDef.BridgeEncryptionAlgorithm = "SASProprietary"; serverDef.BridgeEncryptionLevel = EncryptionLevels.EncryptUserAndPassword; serverDef.MachineDNSName = DnsName; serverDef.Port = Port; serverDef.Protocol = Protocols.ProtocolBridge; // ************************************************ // Create the connection. // ************************************************ sasWorkspace = (Workspace) objFactory.CreateObjectByServer("BridgeConnection", true, serverDef, UserId, Password); } catch (Exception ex) { util.HandleError(MethodBase.GetCurrentMethod().Name, ex); return null; } return sasWorkspace; }
/// <summary> /// Submits SAS code using the IOM interface /// </summary> /// <param name="sasCode">The SAS statements to be submitted</param> public SasRunObject SubmitSasCode(string sasCode) { try { Workspace ws = CreateWorkspace(); Common.StepError = false; Common.SasLanguageService.Reset(); Common.SasLanguageService = ws.LanguageService; Common.SasLanguageService.Submit(sasCode); Common.SasLanguageService.StepError += SasLanguageService_StepError; Common.SasLanguageService.LineSeparator = "\r\n"; var sro = new SasRunObject(); sro.SasCode = sasCode; sro.SasLog = Common.SasLanguageService.FlushLog(Common.LogCharactersToReturn); sro.SasList = Common.SasLanguageService.FlushList(Common.ListCharactersToReturn).Replace("\f", ""); return sro; } catch (Exception ex) { util.HandleError(MethodBase.GetCurrentMethod().Name, ex); return null; } }
Chris Hemendinger has a lot of useful information in his book on Amazon. Highly recommend buying it and using it.
Skip VBA: horrible technology. Loook at ESharper for Excel. Haven't used but was investigating tonight and will probably test.
... View more