topic Re: When run Excel macro second time I get "Run-time error '-2146233088 (80131500)': in SAS Enterprise Guide

When run Excel macro second time I get "Run-time error '-2146233088 (80131500)':

konnos — Mon, 18 Feb 2019 07:11:12 GMT

Dear All, I am trying to create a macro in EXCEL to run multiple SAS Enterprise Guide .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: When run Excel macro second time I get "Run-time error '-2146233088 (80131500)':

konnos — Mon, 18 Feb 2019 07:11:59 GMT

How can I post it with Format. it always makes the text continues

Re: When run Excel macro second time I get "Run-time error '-2146233088 (80131500)':

Kurt_Bremser — Mon, 18 Feb 2019 07:51:02 GMT

Use the {i} button for non-SAS code, text files, logs. Use the "little running man" for SAS code.

Re: When run Excel macro second time I get "Run-time error '-2146233088 (80131500)':

Kurt_Bremser — Mon, 18 Feb 2019 07:55:11 GMT

Software that behaves different on subsequent runs points to an uninitialized resource, where the state from the first execution carries over to the following executions.

Re: When run Excel macro second time I get "Run-time error '-2146233088 (80131500)':

konnos — Mon, 18 Feb 2019 09:03:48 GMT

Thank you for your reply. I am new in this, can you please explain what is the {i} button please.

Re: When run Excel macro second time I get "Run-time error '-2146233088 (80131500)':

Kurt_Bremser — Mon, 18 Feb 2019 09:07:23 GMT

@konnos wrote:
Thank you for your reply. I am new in this, can you please explain what is the {i} button please.

You see it on top of the main posting window, it's the 6th icon; "little running man" is the 7th.

Re: When run Excel macro second time I get "Run-time error '-2146233088 (80131500)':

konnos — Mon, 18 Feb 2019 09:29:18 GMT

Sorry Kurt, I can not find any icons on top of the Posting window. I have attached a .txt file with my question. (I clicked reply, and it only allows me to write something in the box. Either "Rich Text", "HTML", and "Preview". No other options. Thanks

Re: When run Excel macro second time I get "Run-time error '-2146233088 (80131500)':

Kurt_Bremser — Mon, 18 Feb 2019 09:32:37 GMT

Make sure you are in "Rich Text" mode when posting.

Re: When run Excel macro second time I get "Run-time error '-2146233088 (80131500)':

Kurt_Bremser — Mon, 18 Feb 2019 09:39:38 GMT

My other argument is still valid. Different behaviour on subsequent runs points to some uninitialized resource, where the 'null' or random value encountered in the first run is replaced with some (default?) value and causes the second and following runs to fail.

That is an observation from 4 decades of programming, not specific to VBA. I don't even touch that with a ten-yard pole, but you should review your code along this aspect. It might be that some module you use is not safe for reuse in the same context, and mandates a restart of Excel.

Re: When run Excel macro second time I get "Run-time error '-2146233088 (80131500)':

konnos — Tue, 19 Feb 2019 14:36:52 GMT

Hi Kurt, Thank you for your comment. I followed your suggestion and have tried to figure out which variable is the one causing this issue and I tried to initialize, clear, all variables before and after finishing the macro, but the issue still persists. I starting to believe that this is a bug of CreateObject("SASEGObjectModel.Application.7.1") Thanks,

Re: When run Excel macro second time I get "Run-time error '-2146233088 (80131500)':

Kurt_Bremser — Tue, 19 Feb 2019 14:44:43 GMT

If you could successfully narrow it down to this, opening a track with SAS technical support is now in order.