Run Excel Macro to run multiple SAS egp

[konnos]

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.

[VDD]

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-dat...

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

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

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

[konnos]

{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}