B West
B West

Reputation: 57

Excel VBA Shell "file not found"

For the life of me, I can't get this shell procedure to work with variables. I'm trying to execute POSTRESULTS6 which then processes the csv file. The "DISPLAY" argument tells POSTRESULTS6 to show a progress window as it processes the csv.

Dim MyAppID As Variant
Dim stExecute As String: stExecute = """C:\Program Files (x86)\PerkinElmer\LABWORKS64\POSTRESULTS6.exe"""
Dim stFile As String: stFile = "INFILE:C:\PostResult6\Multi-Component_Data_Import.csv"
Dim stFull As String: stFull = "stExecute & "" "" & stFile"
MyAppID = Shell("stFull" & "" "" & DISPLAY, vbNormalFocus)
AppActivate (MyAppID)

The procedure works as expected when I type it all out in one single line like this:

MyAppID = Shell("""C:\Program Files (x86)\PerkinElmer\LABWORKS64\POSTRESULTS6.exe"" & "" "" & INFILE:C:\PostResult6\Multi-Component_Data_Import.csv" & "" "" & DISPLAY, vbNormalFocus)

The reason that I want to use variables is to shorten the lines of code and possibly make the procedure dynamic for the csv file name.

I'm guessing that it has something to do with spaces but I can't figure out where my mistake is. I also can't figure out how to continue the long string of code to the next line. The space-underscore doesn't seem to work.

As an aside, when my shell procedure was very simple, I didn't have to declare the MyAppID variable. As it got more complex, I started getting errors about MyAppID not being defined. Does anyone know why this is?

Any help is greatly appreciated.

Edit: The Error that I get when I run the code with variables is "Run-time error '53': File not found"

I know that the file paths assigned to the variables are correct. I tested them in windows explorer.

Upvotes: 2

Views: 8391

Answers (3)

Michal Nies.
Michal Nies.

Reputation: 91

My perspective for using variables in Shell:

MyAppID as variant
Dim Program as string
Dim File as string

Program = """Your path to program"""
File = """Your path to file"""

MyAppID = Shell(Program & " " & File, vbNormalFocus)

Additional comments:

  • Single quotes can be used (e.g. Program = "Your path to program") but then VBA may have problem to compile properly if you have spaces in folders names and would return an error.

Upvotes: 0

Amen Jlili
Amen Jlili

Reputation: 1934

You've got messed up quotes. stFull in your last line is being evaluated as a literal not as a string. Here's my attempt to solve your quotes debacle.

    Dim stExecute As String: stExecute = "C:\Program Files (x86)\PerkinElmer\LABWORKS64\POSTRESULTS6.exe"
Dim stFile As String: stFile = "INFILE:C:\PostResult6\Multi-Component_Data_Import.csv"
Dim StFull As String
StFull = """" & """" & """" & stExecute & """" & """" & " & """" """" & " & stFile & """"
MyAppID = Shell(stFull & "" "" & DISPLAY, vbNormalFocus)
AppActivate (MyAppID)

Outputting StFull:

enter image description here

Upvotes: 0

Tony Dallimore
Tony Dallimore

Reputation: 12403

Your problem is, for example: stFull = "stExecute & "" "" & stFile"

You have stExecute within quotes which makes it a literal not a variable.

I think this is correct:

  Dim MyAppID As Variant
  Dim stExecute As String: stExecute = """C:\Program Files (x86)\PerkinElmer\LABWORKS64\POSTRESULTS6.exe"""
  Dim stFile As String: stFile = "INFILE:C:\PostResult6\Multi-Component_Data_Import.csv"
  Dim stFull As String: stFull = stExecute & " " & stFile
  MyAppID = Shell(stFull & " DISPLAY", vbNormalFocus)

Upvotes: 1

Related Questions