topic Run Excel Macro to run multiple SAS egp in New SAS User

Run Excel Macro to run multiple SAS egp

konnos — Fri, 15 Feb 2019 09:51:52 GMT

Dear All, I am trying to create a macro to run multiple SAS .egp programs from Excel. (I am stack 2 days now) I managed to write a code to run one .egp program but if I run the macro again second time before I close the Excel workbook I get a run time error: "Run-time error '-2146233088 (80131500)': Path is not a directory 'C:': The filename, directory name, or volume label syntax is incorrect." This is very strange since I am not working on C: drive. Please see below the excel macro which crashes the second time I run it at line: Set Proj = ApplicationObj.Open(Path & "SAS1.egp", "") Sub RunFor() Dim ApplicationObj As Object 'Application Dim Proj As Object 'Project object Dim sasProgram As Object 'Code object (SAS program) Dim log1 As String Dim parmList As Variant Dim parm As Variant Dim Path As String Dim Full_Date As Variant Path = "M:\REPORT TO UNIT\KPIs_reports\Run SAS from Excel tests\" Worksheets("KPIs").Activate Full_Date = Cells(9, "C").Value 'On Error GoTo ErrHandler Set ApplicationObj = CreateObject("SASEGObjectModel.Application.7.1") Set Proj = ApplicationObj.Open(Path & "SAS1.egp", "") 'Set Proj = ApplicationObj.Open(Path & "SAS1.egp", "") Set parmList = Proj.Parameters MsgBox "Project has " & parmList.Count & " parameters." Set parm = parmList.Item(0) 'MsgBox parm.Name & " parameter has default value of " & parm.DefaultValue MsgBox parm.Name & " parameter" parm.Value = Full_Date MsgBox parm.Name & " parameter has been set to value of " & parm.Value Proj.Run 'Set Proj = Nothing 'Set ApplicationObj = Nothing Proj.SaveAs "M:\REPORT TO UNIT\KPIs_reports\Run SAS from Excel tests\SAS1.egp" Proj.Close ApplicationObj.Quit ''''''''&Preivous_Date ,is the prompt in SAS ActiveWorkbook.RefreshAll End End Sub Please let me know your thoughts. Thanks in advance.

Re: Run Excel Macro to run multiple SAS egp

VDD — Sat, 16 Feb 2019 14:47:00 GMT

Please post you questions and code in a readable format.

https://communities.sas.com/t5/help/faqpage/faq-category-id/posting#posting

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Maxims of Maximally Efficient SAS Programmers
https://communities.sas.com/t5/SAS-Communities-Library/Maxims-of-Maximally-Efficient-SAS-Programmers/ta-p/352068

How to convert datasets to data steps
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

The macro for direct download
https://gist.githubusercontent.com/statgeek/bcc55940dd825a13b9c8ca40a904cba9/raw/865d2cf18f5150b8e887218dde0fc3951d0ff15b/data2datastep.sas

Re: Run Excel Macro to run multiple SAS egp

konnos — Tue, 19 Feb 2019 14:27:54 GMT

{Hi VDD, Thanks for the reply. I am trying to find how to format my post but I do not have the options as mentioned in your first link in section "How can I add SAS syntax to a post?" I am using Internet Explorer, is that is issue? Thanks}