15

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?

brentf
  • 411
  • 2
  • 8
  • 14
  • This is a wild guess but... Comment out this line: `Set wsh = VBA.CreateObject("WScript.Shell")`. Replace `wsh.Run` with just `Shell`. What happens? – WGS Jan 21 '14 at 19:30
  • That works - that's actually what I am doing currently as a work-around. The problem is that I need the program to provide feedback on when the model has completed running (so I can know when to run the output generation code)... – brentf Jan 21 '14 at 19:35
  • I noticed that my directory path on the "shared" version has a space in one of the folder names - could that be throwing things off? – brentf Jan 21 '14 at 19:35

6 Answers6

18

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.

Paul Schreiber
  • 12,531
  • 4
  • 41
  • 63
sobersoup
  • 189
  • 1
  • 3
4

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

Jezz81
  • 41
  • 1
0

I have determined that the problem appears entirely due to the directory structure including a space. There does not appear to be any issue relating to the local vs. shared directory.

If someone knows an easy fix to get around the directory having a space in it, please let me know!

brentf
  • 411
  • 2
  • 8
  • 14
0

I have noticed that the problem only seems to occur when more than one argument is passed on to the program/script that is run.

If the second argument contains a filepath with a space, even when the necessary """" are present, the error occurs.

Can't find any solution at this point...

bnqprv
  • 1
  • 1
0

found this great reference explaining triple quotes when there is a space in the path. Here is a portion of it in case it gets taken down or moves:

if you had wanted to open a file

c:\My Files\Text File.txt and your shell required that this be wrapped in "", then you'd write the string like this

PID = Shell("notepad ""c:\My Files\Text File.txt""", vbNormalFocus)

The same goes for any path you need to specify for the actual command/script name. In this example I'm calling a batch file (Text Parser.bat) to process the text file (Text File.txt):

PID = Shell("""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt""", vbNormalFocus)

All of those """ look a bit strange but let me explain. The first and last " mark the beginning and end of the string that specifies the program being called, including any parameters, switches and file(s) it will use:

"""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt""" If we remove those " we are left with the Program string itself, which is composed of two separate strings, one for the path\batch file, and the other for the path\file the batch file will use .

""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt"" When this is passed to the Shell one of the double " is removed so what is actually seen in the Shell is

"c:\My Scripts\Text Parser.bat" "c:\My Files\Text File.txt" Which looks like two normally delimited strings.

gregV
  • 987
  • 9
  • 28
0

This thread is ancient but I ran across the same issue and devised a solution.

Write some code to put that command into a batch file in C:\Temp... that way Shell or Wscript.Shell will receive a digestible "C:\Temp\thing.bat" as the argument.

Worked like a charm for me. :)