Error running Shell object / commands through Excel VBA

Question

I am running a C++ program through my VB code, and I am having trouble getting my code to run on a shared drive vs. on a local computer. My program generates a set of assumptions, then runs those assumptions through a C++ model, then picks up the model output and prepares it for viewing in the VB workbook.

The code below works fine when I have the workbook saved in a local directory on my C drive, but when I upload it to my company's shared drive, I get the following error:

"Run-time error '-2147024894 (80070002)': Method 'Run' of object 'IWshShell3' failed"

Code:

'---------------------------------------------------------
' SECTION III - RUN THE MODEL AS C++ EXECUTABLE
'---------------------------------------------------------
Dim ModelDirectoryPath As String
Dim ModelExecutableName As String
Dim ModelFullString As String

' First build the command string
Application.StatusBar = "Running C++ Model..."

ModelDirectoryPath = Range("ModelFilePath").value
ModelExecutableName = Range("ModelFileName").value
ModelFullString = ModelDirectoryPath & ModelExecutableName
ModelFullString = ModelFullString & " " & ScenarioCounter & " " & NumDeals _
                  & " " & ModelRunTimeStamp & " " & Settle_YYMMDD

' Run the program
Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim errorCode As Integer

errorCode = wsh.Run(ModelFullString, windowStyle, waitOnReturn)

If errorCode = 0 Then
    ' MsgBox "C++ Model Completed without Errors."
Else
    MsgBox "Program exited with error code " & errorCode & "."
End If

Application.StatusBar = "C++ Model Complete"

Any thoughts?

Answer 1

The error does come from the directory having a space in it:

C:\Users\myname\this is my folder\myexe.exe

A simple workaround does the trick:

wsh.Run(Chr(34) & YourFullPathDirectoryWithSpaces & "\myexe.exe" & Chr(34))

Chr(34) is a double quote.

There was an issue with .Run taking a two line property.

Tested it on Excel 2010/Win32.

Answer 2

Had the same problem, alternative solution:

wsh.CurrentDirectory = exePath
wsh.Run(exeName & " " & cmdArgs, windowStyle, waitOnReturn)

Key point being to set the CurrentDirectory property of the shell