Excel VBA Shell "file not found"

Question

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.

You have a problem with your quotes.

Amen Jlili
– Amen Jlili

2015-02-12 21:15:27 +00:00
Commented Feb 12, 2015 at 21:15 — Amen Jlili
– Amen Jlili, Commented Feb 12, 2015 at 21:15

Tony Dallimore · Accepted Answer · 2015-02-12 21:30:14Z

1

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)

answered Feb 12, 2015 at 21:30

Tony Dallimore

12.4k7 gold badges35 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

B West Over a year ago

That did the trick with the least quotations. Thank you very much. Do you happen to know how to continue a long string, like my second example, to the next line? Space underscore doesn't work.

Tony Dallimore Over a year ago

@BWest You have to end one literal, start another and use & to concatenate them. For example: stExecute = """C:\Program Files (x86)\" & _ newline "PerkinElmer\LABWORKS64\POSTRESULTS6.exe""". There must be no extra spaces within the literals.

Amen Jlili · Accepted Answer · 2015-02-12 21:58:00Z

0

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

answered Feb 12, 2015 at 21:58

Amen Jlili

1,9444 gold badges28 silver badges52 bronze badges

Comments

Michal Nies. · Accepted Answer · 2019-03-04 10:50:55Z

0

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.

answered Mar 4, 2019 at 10:50

Michal Nies.

919 bronze badges

Collectives™ on Stack Overflow

Excel VBA Shell "file not found"

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related