In VBA, this same exact string does not execute....
Code:
shell strCommmand 'does nothing'
debug.print strCommand 'returns exactly the same string as typed into CMD prompt above.
Note: The reason I am enclosing input.pdf and output.pdf arguments in CHR(34) as seen above in the string to handle any spaces in the file path. (strCommand is built dynamically). 256 is the third argument, not enclosed in CHR(34)..
Still unsure why the VBA Shell function has difficulty with this, and the CMD Prompt interface doesn't.
The issue is that the pipe command > "C:\Users\Dev\log.txt"is being treated as an argument to the executable C:\Users\Dev\Scripts\Script.exe, and not a pipe to the output. So, must I escape the script's args somehow and then append the pipe cmd?
Why this is NOT true when I type the exact string into CMD--- is beyond me.
Hi. Not in front of a computer now; but if the string expression works with cmd but not with shell, then maybe shell the cmd.exe and pass the string as an argument to cmd. Just a thought...
They are different things.
CMD is a command line interpreter. It provides the functionality to use < or > for IO redirection. If you use the Shell function to access the shell directly, you cannot use functionality provided by CMD.
However, you can use Shell to invoke CMD and then pass the arguments to CMD.
After several hours of mashing on the keyboard, I solved a multitude of problems to achieve the end result I desired.
'------------------------------------------------
In regards to the specific OP question:
@sonic8 pointed me in the right direction. I can't pipe directly from the shell. To do so, required prompting cmd.exe first and then entering the command for the prompt...
Further--- this debacle led me down a path to produce the following VBA Function...
Runs a command, takes arguments
is immaterial to file path spaces - what a headache Miscrosoft made 30 years ago to have a space in a filepath. Sheesh.
is immaterial to spaces in arguments to the command/path
Runs the command silently in the background
Returns an output from the cmd prompt
Waits to complete before returning
If you've ever tried to achieve this in VBA, satisfying every one of those requirements is painfully difficult. Some comments on this...
1.) Throw the native VBA.Shell function away for this. Not enough functionality.
2.) WScript.Shell has 2 methods of "executing", .Run() and .Exec(). Exec() you can read the output of the command, Run() you cannot. HOWEVER>>>> Exec() cannot 'WaitOnReturn'. Hence, the reason to pipe this output. Thats how this function achieves the ability to read the console.
3.) This is the format that a command string must take in order to correctly function when white space characters are present in the command string:
The path is enclosed in chr(34), as well as each individual argument. All delimited by spaces. And finally, the entire command is enclosed in spaces. chr(34) (12.27.21 - mistake, i meant chr(34) not spaces.)
Function RunCMD(Path As String, LITERAL As Boolean, DEBUG_ As Boolean, ParamArray args() As Variant) As String
'9-23-21 IronFelix717 @ Access-Programmers.co.uk
'-----------------------------------------------
'RUNS A COMMAND SILENTLY IN BACKGROUND,
'WAITS UNTIL CMD IS EXECUTED, RETURNS PROMPT OUTPUT AS STRING
'ALLOWS FOR SPACES IN [PATH] AND [args]
'------------------------------------------------
'USE:
'[PATH] = COMMAND TO EXECUTE, OR PATH OF .EXE
'[LITERAL] = FALSE: [PATH] and [args] ENCLOSED IN CHR(34), TRUE : NOT ENCLOSED
'[args] = CMD LINE ARGUMENTS TO PASS TO [PATH]
'[DEBUG_] = Show window
'------------------------------------------------
Dim arg As Variant
Dim cmd As String
Dim sh As Object
Dim OutputFile As String
Dim FSO As Object
Dim WindowType As Integer
On Error GoTo Handler
Set sh = CreateObject("WScript.shell")
Set FSO = CreateObject("Scripting.FileSystemObject")
'APPEND CHR(34)
If LITERAL = False Then
Path = Chr(34) & Path & Chr(34)
End If
'GENERATE CMD + ARGS STRING
cmd = Path & " " 'default
If Not IsMissing(args) Then
For Each arg In args
If LITERAL = False Then
cmd = cmd & Chr(34) & arg & Chr(34) & " "
Else
cmd = cmd & arg & " "
End If
Next
End If
cmd = Left(cmd, Len(cmd) - 1)
'KILL EXISTING FILE, GENERATE OUTPUT FILE NAME, ADD PIPE CMD
OutputFile = Environ("TEMP") & "\VBARunCMD_Output.txt"
If dir(OutputFile) <> "" Then Kill OutputFile
cmd = cmd & " > " & Chr(34) & OutputFile & Chr(34)
'SURROUND ENTIRE CMD WITH CHR(34)
cmd = Chr(34) & cmd & Chr(34)
'BUILD SHELL STRING FOR CMD:
If DEBUG_ = True Then
cmd = "cmd.exe /K " & cmd
Else
cmd = "cmd.exe /C " & cmd 'SWITCHES: /C = RUN AND CLOSE, K = RUN AND RETURN TO PROMPT
End If
'Debug.Print cmd
'RUN
If DEBUG_ = True Then WindowType = 1 ' 0=hid, 1 = show
sh.Run cmd, WindowType, True
If dir(OutputFile) = "" Then
RunCMD = "Failed."
Else
If FileLen(OutputFile) > 0 Then
RunCMD = FSO.OpenTextFile(OutputFile).ReadAll
End If
End If
Exit Function
Handler:
MsgBox "RunCMD(): Error: " & vbCrLf & Err.Number & vbCrLf & Err.Description
End Function
And it can be used like...
Diff:
CMD = RunCMD("C:\Scripts\My Script.exe", False, False, "my argument 1", "my argument 2")
CMD = RunCMD("Timeout 5",True, False) 'Run Timeout command, Timeout command is LITERAL
cmd = RunCMD("Timeout", True, True, 5) 'Same as above, except with arg, show debug window'
CMD = RunCMD(chr(34) & "C:\ My Script.exe" & chr(34), True, False, 30050, 0) 'script with args not encased in chr(34)
After several hours of mashing on the keyboard, I solved a multitude of problems to achieve the end result I desired.
'------------------------------------------------
In regards to the specific OP question:
@sonic8 pointed me in the right direction. I can't pipe directly from the shell. To do so, required prompting cmd.exe first and then entering the command for the prompt...
Further--- this debacle led me down a path to produce the following VBA Function...
Runs a command, takes arguments
is immaterial to file path spaces - what a headache Miscrosoft made 30 years ago to have a space in a filepath. Sheesh.
is immaterial to spaces in arguments to the command/path
Runs the command silently in the background
Returns an output from the cmd prompt
Waits to complete before returning
If you've ever tried to achieve this in VBA, satisfying every one of those requirements is painfully difficult. Some comments on this...
1.) Throw the native VBA.Shell function away for this. Not enough functionality.
2.) WScript.Shell has 2 methods of "executing", .Run() and .Exec(). Exec() you can read the output of the command, Run() you cannot. HOWEVER>>>> Exec() cannot 'WaitOnReturn'. Hence, the reason to pipe this output. Thats how this function achieves the ability to read the console.
3.) This is the format that a command string must take in order to correctly function when white space characters are present in the command string:
Function RunCMD(Path As String, LITERAL As Boolean, DEBUG_ As Boolean, ParamArray args() As Variant) As String
'9-23-21 IronFelix717 @ Access-Programmers.co.uk
'-----------------------------------------------
'RUNS A COMMAND SILENTLY IN BACKGROUND,
'WAITS UNTIL CMD IS EXECUTED, RETURNS PROMPT OUTPUT AS STRING
'ALLOWS FOR SPACES IN [PATH] AND [args]
'------------------------------------------------
'USE:
'[PATH] = COMMAND TO EXECUTE, OR PATH OF .EXE
'[LITERAL] = FALSE: [PATH] and [args] ENCLOSED IN CHR(34), TRUE : NOT ENCLOSED
'[args] = CMD LINE ARGUMENTS TO PASS TO [PATH]
'[DEBUG_] = Show window
'------------------------------------------------
Dim arg As Variant
Dim cmd As String
Dim sh As Object
Dim OutputFile As String
Dim FSO As Object
Dim WindowType As Integer
On Error GoTo Handler
Set sh = CreateObject("WScript.shell")
Set FSO = CreateObject("Scripting.FileSystemObject")
'APPEND CHR(34)
If LITERAL = False Then
Path = Chr(34) & Path & Chr(34)
End If
'GENERATE CMD + ARGS STRING
cmd = Path & " " 'default
If Not IsMissing(args) Then
For Each arg In args
If LITERAL = False Then
cmd = cmd & Chr(34) & arg & Chr(34) & " "
Else
cmd = cmd & arg & " "
End If
Next
End If
cmd = Left(cmd, Len(cmd) - 1)
'KILL EXISTING FILE, GENERATE OUTPUT FILE NAME, ADD PIPE CMD
OutputFile = Environ("TEMP") & "\VBARunCMD_Output.txt"
If dir(OutputFile) <> "" Then Kill OutputFile
cmd = cmd & " > " & Chr(34) & OutputFile & Chr(34)
'SURROUND ENTIRE CMD WITH CHR(34)
cmd = Chr(34) & cmd & Chr(34)
'BUILD SHELL STRING FOR CMD:
If DEBUG_ = True Then
cmd = "cmd.exe /K " & cmd
Else
cmd = "cmd.exe /C " & cmd 'SWITCHES: /C = RUN AND CLOSE, K = RUN AND RETURN TO PROMPT
End If
'Debug.Print cmd
'RUN
If DEBUG_ = True Then WindowType = 1 ' 0=hid, 1 = show
sh.Run cmd, WindowType, True
If dir(OutputFile) = "" Then
RunCMD = "Failed."
Else
If FileLen(OutputFile) > 0 Then
RunCMD = FSO.OpenTextFile(OutputFile).ReadAll
End If
End If
Exit Function
Handler:
MsgBox "RunCMD(): Error: " & vbCrLf & Err.Number & vbCrLf & Err.Description
End Function
And it can be used like...
Diff:
CMD = RunCMD("C:\Scripts\My Script.exe", False, False, "my argument 1", "my argument 2")
CMD = RunCMD("Timeout 5",True, False) 'Run Timeout command, Timeout command is LITERAL
cmd = RunCMD("Timeout", True, True, 5) 'Same as above, except with arg, show debug window'
CMD = RunCMD(chr(34) & "C:\ My Script.exe" & chr(34), True, False, 30050, 0) 'script with args not encased in chr(34)
ironfelix, thanks for posting this code, it's a real find.
I am having issues and would like your input. As I've understood from your examples, one may either stuff the parameters into the Path variable, or else send as an array via ParamArray(). In my testing I've found cases where in calling a powershell script RunCMD() throws an error, but running what is assembled by RunCMD in a command prompt is successful.
I would prefer to call with named arguments. Here is an example call that throws an error when executed via RunCMD(), when run in a command prompt works. I've used the value offered by Debug.Print cmd in RunCMD(), minus the cmd.exe /C prefix
The error that is thrown when called by RunCMD is
The property 'Automated' cannot be found on this object. Verify that the property exists and can be set.
That might be from the powershell code, I'm not really sure. But why does the call work in cmd?
Glad you found some use to the script. I'm still using it for one specific .exe in an Access app.
My brain is extremely rusty right now, especially on this topic. Which is why I tried to make the script as bone-head proof as I could at the time, so that when my bonehead returned to the issue, I would have more chances at success LOL.
It cannot say why your script isn't working with the 'CMD' appended to the string. I dont have that specific cmd to test. Perhaps it has something to do with powershell vs CMD? Not sure.
All I can say for now is, you can still use the function and modify it to suit your specific need maybe. Remove the section that adds the CMD string and see if SH.run will work with the un-appended command.
Two boneheads are better than one...I'll continue to experiment and will report back if any progress.
It is a nice bit of code, appreciate that you put it up here.
ironfelix, thanks for posting this code, it's a real find.
I am having issues and would like your input. As I've understood from your examples, one may either stuff the parameters into the Path variable, or else send as an array via ParamArray(). In my testing I've found cases where in calling a powershell script RunCMD() throws an error, but running what is assembled by RunCMD in a command prompt is successful.
I would prefer to call with named arguments. Here is an example call that throws an error when executed via RunCMD(), when run in a command prompt works. I've used the value offered by Debug.Print cmd in RunCMD(), minus the cmd.exe /C prefix
The error that is thrown when called by RunCMD is
The property 'Automated' cannot be found on this object. Verify that the property exists and can be set.
That might be from the powershell code, I'm not really sure. But why does the call work in cmd?
The sample code you posted that's supposed to work when typed in the command prompt has a leading double quote. Is that correct? You're supposed to have it like that for it to work? I am asking because syntax is important, so I could maybe offer a way to duplicate it via VBA. Cheers!
I know it looks strange, doesn't it? But the full structure that debug emits does seem to make sense, or at least it could make sense. After the leading
cmd.exe /C
the entire string is wrapped with a pair of doublequotes, and inside that string two other strings are wrapped in doublequotes (the path to the ps1 file and the path to the output debug file). The full output of debug, when run at a command prompt, does work correctly.
I know it looks strange, doesn't it? But the full structure that debug emits does seem to make sense, or at least it could make sense. After the leading
cmd.exe /C
the entire string is wrapped with a pair of doublequotes, and inside that string two other strings are wrapped in doublequotes (the path to the ps1 file and the path to the output debug file). The full output of debug, when run at a command prompt, does work correctly.
Hi. Just to clarify, I just need to see the exact syntax you entered in the command prompt that you said worked. Then, we can try to see if we can adjust the one that Debug.Print is supposed to return, if it's not the same as the one that worked when you manually entered it.
Right...both of the lines I posted work in commend prompt. The upper one that starts with cmd.exe /C is an exactly copy of what RunCMD() debug output for the cmd variable contains. The lower one is simply a reduction of the original, as I figured the leading cmd.exe /C was redundant when entered into command prompt itself.
Right...both of the lines I posted work in commend prompt. The upper one that starts with cmd.exe /C is an exactly copy of what RunCMD() debug output for the cmd variable contains. The lower one is simply a reduction of the original, as I figured the leading cmd.exe /C was redundant when entered into command prompt itself.
Hi. Sorry if I am not making any sense or just having difficulty understanding you. One more try please, can you maybe post a screenshot of the command prompt with the command syntax you used that worked?
Re-reading the post you just made, it seems you're saying the result of the Debug.Print was copied and pasted into the command prompt, and it worked. Is that correct?
Yes that's the main mystery, why the debug from cmd variable in RunCMD() executes fine in command prompt but fails when RunCMD tries it.
I can't post the screenshot without altering it because of org name embedded etc.
Thanks!
Yes that's the main mystery, why the debug from cmd variable in RunCMD() executes fine in command prompt but fails when RunCMD tries it.
I can't post the screenshot without altering it because of org name embedded etc.
Thanks!
First, ensure the arguments you are passing meet the requirements I describe in my update above.
The basic format for allowing whitespace characters is:
""Path to Script.exe" "Argument1" "Argument 2" "Argument 3"
Please not that this format is not specifically required. However, it is required for a path/arguments that have spaces!
Using the 'LITERAL' parameter would alternatively allow you to do this.... PathToScript.Exe argument1 argument2 argument3
Thus, the output is literal and contains no CHR(34), which is required in some instances where the script needs a literal value. However, this will not allow the protection of whitespace characters in the path or arguments! LITERAL means it takes the path and command verbatim and attempts to run a raw, literal string of path and arguments. Should an argument or path need protection from whitespace characters while allowing LITERAL=TRUE, then it is up to the caller to specifically append (Chr34) to that argument/path prior to passing to the function.
And of course, either of those two commands (literal or non-literal) are appended with cmd.exe /C . To run the command in CMD prompt.
And finally, depended with the pipe command, which allows this function to grab the prompt's return text. > "VBARunCMD_Output.txt"
If appears you have LITERAL set to true, which your script takes a lot of arguments and they don't appear to be strings, so you probably need literal values passed. Surely you know that any unintentional spaces in the entire arg string will be interpreted as a unique argument. For example, if your argument to pass to the script is... Scope3 and your command contains Scope 3, Both Scope and 3 will be interpreted as separate commands.
I believe you are aware of these rules, but I am just clarifying.
UPDATED CODE:
Here is an update to the code after some months.
1.) Code now supports ability to pass an array as a parameter in the param array.
Ex: RunCMD(mypath,false,false,MyFilesArray)
2.) Code has improved debug.prints to make troubleshooting more understandable to new users of the function.
Example:
Code:
Sub Test()
Dim MyArray As Variant
MyArray = Array("VBA", "IS", "POWERFUL")
MsgBox RunCMD("echo", True, False, MyArray)
MsgBox RunCMD("Echo VBA IS AWESOME!", True, False)
End Sub
Code:
Code:
Function RunCMD(path As String, LITERAL As Boolean, DEBUG_ As Boolean, ParamArray args() As Variant) As String
'9-23-21 IronFelix717 @ Access-Programmers.co.uk
'10-18-21 - Updated to allow ability to pass an array as a param array argument
'12-27-21 - Updated debug.print locations for better troubleshooting.
'-----------------------------------------------
'RUNS A COMMAND SILENTLY IN BACKGROUND,
'WAITS UNTIL CMD IS EXECUTED, RETURNS PROMPT OUTPUT AS STRING
'ALLOWS FOR SPACES IN [PATH] AND [args]
'------------------------------------------------
'USE:
'[PATH] = COMMAND TO EXECUTE, OR PATH OF .EXE
'[LITERAL] = FALSE: [PATH] and [args] ENCLOSED IN CHR(34), TRUE = NOT ENCLOSED
'[args] = CMD LINE ARGUMENTS TO PASS TO [PATH]
'[DEBUG_] = Show window
'------------------------------------------------
Dim arg As Variant
Dim cmd As String
Dim sh As Object
Dim OutputFile As String
Dim FSO As Object
Dim WindowType As Integer
Dim args2 As Variant
Dim arg2 As Variant
On Error GoTo handler
Set sh = CreateObject("WScript.shell")
Set FSO = CreateObject("Scripting.FileSystemObject")
'APPEND CHR(34)
If LITERAL = False Then
path = Chr(34) & path & Chr(34)
End If
'GENERATE CMD + ARGS STRING
cmd = path & " " 'default
If Not IsMissing(args) Then
For Each arg In args
If VarType(arg) = 8204 Then 'user passed an array as an argument
args2 = arg
For Each arg2 In args2
If LITERAL = False Then
cmd = cmd & Chr(34) & arg2 & Chr(34) & " "
Else
cmd = cmd & arg2 & " "
End If
Next arg2
Else
If LITERAL = False Then
cmd = cmd & Chr(34) & arg & Chr(34) & " "
Else
cmd = cmd & arg & " "
End If
End If
Next arg
End If
cmd = Left(cmd, Len(cmd) - 1)
If DEBUG_ = True Then
'PRINT THE RAW COMMAND, BEFORE PIPING, BEFORE APPENDING CMD.EXE
Debug.Print "RunCMD.Debug: Raw Command = " & cmd
End If
'KILL EXISTING FILE, GENERATE OUTPUT FILE NAME, ADD PIPE CMD
OutputFile = Environ("TEMP") & "\VBARunCMD_Output.txt"
If dir(OutputFile) <> "" Then Kill OutputFile
cmd = cmd & " > " & Chr(34) & OutputFile & Chr(34)
'SURROUND ENTIRE CMD WITH CHR(34)
cmd = Chr(34) & cmd & Chr(34)
'BUILD SHELL STRING FOR CMD:
If DEBUG_ = True Then
cmd = "cmd.exe /K " & cmd
Else
cmd = "cmd.exe /C " & cmd 'SWITCHES: /C = RUN AND CLOSE, K = RUN AND RETURN TO PROMPT
End If
'RUN
If DEBUG_ = True Then
WindowType = 1 ' 0=hid, 1 = show
Debug.Print vbCrLf
'PRINT THE FINAL COMMAND THAT WSHELL WILL RUN!
Debug.Print "RunCMD.Debug: Final Shell Command (Piped) = " & cmd
End If
sh.Run cmd, WindowType, True
If dir(OutputFile) = "" Then
RunCMD = "Failed."
Else
If FileLen(OutputFile) > 0 Then
RunCMD = FSO.OpenTextFile(OutputFile).ReadAll
End If
End If
Exit Function
handler:
MsgBox "RunCMD(): Error: " & vbCrLf & Err.Number & vbCrLf & Err.Description
End Function
