Shell command does not return the correct result

Question

In my program I want to do the following command:

RetVal = Shell("dir > temp", 1)

When I do that I get the following error:

Run-time error "53": File not found.

Just doing Shell("dir", 1) gives me the same error. However, if I do Shell("help", 1), it works.

The dir > temp command works fine in the command window. I tried using Shell with the following commands: dir, cd, ver, time, date, help. The only one of these that works is help.

I expect to get a directory listing when I do Shell("dir > temp", 1) that I can do other things with temp. Instead, I get "File not found".

Assuming (dangerously) that you really mean dir > temp, you may want to specify a full path for temp. Where the file ends up when running from a command prompt may be different than where it ends up when being executed by your VBA code. It never hurts to be fully explicit. — FreeMan
– FreeMan, Commented Jul 15, 2019 at 17:31

41686d6564 · Accepted Answer · 2019-07-15 18:06:24Z

2

Not all commands passed to the Shell function will work as it does in the Command Prompt window. The Shell function is meant to run an executable file, not execute commands:

Runs an executable program and returns a Variant (Double) representing the program's task ID if successful; otherwise, it returns zero.

Think about it like the "Run" utility in Windows.

What you should do is call "cmd.exe" first and pass whatever arguments/commands you need to it. Try something like this:

Dim myCommand As String
myCommand = "dir"
Shell "cmd.exe /S /K" & myCommand, vbNormalFocus

If you want the Command Prompt window to close after executing the command, you can replace the parameter /K with /C. In that case, you might also want to use vbHide instead of vbNormalFocus:

myCommand = "dir > temp"
Shell "cmd.exe /S /C" & myCommand, vbHide

edited Jul 15, 2019 at 18:06

answered Jul 15, 2019 at 17:12

41686d6564

19.8k13 gold badges48 silver badges84 bronze badges

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

4 Comments

Mathieu Guindon Over a year ago

Or simply, Shell "cmd.exe /S /K" & myCommand, vbNormalFocus - unless you also do Call MsgBox("message") and Call Beep and Call DoEvents.

41686d6564 Over a year ago

@MathieuGuindon I don't :-) The OP just used braces in their examples so I didn't want to confuse him/her. After a second thought, Call in itself could be more confusing, so you're right! Also, I just noticed that he/she assigns the returned value to a variable so braces should work anyway.

Mathieu Guindon Over a year ago

Nah, I just have this particular thing with Call never being used consistently by anyone that ever uses it ;-)

41686d6564 Over a year ago

@MathieuGuindon That's a very informative answer. Thank you!

Collectives™ on Stack Overflow

Shell command does not return the correct result

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related