manual entry in cmd window works, VBA executing CMD works, but not VBA when I use run (so I can hide the window)

Question

SECOND EDIT/UPDATE: tried the path change recommendations, did not see any changes to the command string, still does not work. I re-wrote the code to use a fixed text file instead of a random temp file so I could monitor the contents of the file during execution. Able to conclusively show it is the

oShellObject.Run sCommandStringToExecute & " > " & sShellRndTmpFile, 0, True

code line that doesn't behave as expected. Still works with the w32tm command line, but not with the ntpq command line. With ntpq command, no changes made to the file, no error flags. I also tried out (again) the exec version of this problem where the window is supposed to flash a bit before it gets hidden programmatically. I get the expected reslut using exactly the same command string, cut and pasted into the other code. So the same command line works with manual entry into CMD, into PowerShell, and in the .exec code version, not the .run code version.

End of second edit. -------------------

EDIT: more debugging... ntpq -p works if I do .exec instead of .run, but then of course can't hid the cmd window. Extra test code at the end.

This Works: If I run these two commands in manually opened cmd window, or PowerShell window, both give the expected results.

w32tm /stripchart /computer:time.nist.gov /dataonly /samples:3 /rdtsc /period:1

ntpq -p

The second, ntpq -p, is bundled with NTP windows software from the home of the Network Time Protocol project that gives similar information to windows' w32tm when NTP is set up to look at the same time service computer as in the w32tm command.

This Doesn't work: When I try to use these two command string when running CMD functions hidden using the classic "write to file" method shown in SO here and other places, the w32tm version gives the same results as the manual version, but the ntpq version just returns "error".

I read every single one of the recommended links for this question as well as searching OS and Google, and have not found an answer.

I am stuck on next step to troubleshoot the problem...only thing I could think of was to run the commands manually to confirm they work there. I can't imagine it being a administrator privileges issue since I can run them both in CMD line or PowerShell windows opened at normal rights level.

What should I look at next?

Here is the test code.


Option Explicit

Sub TestShellRun()

Dim sCmd  As String, sReturnNTP As String

sCmd = "w32tm /stripchart /computer:time.nist.gov    /dataonly /samples:3 /rdtsc /period:1 "     ' /packetinfo"
sCmd = "%ComSpec% /C %SystemRoot%\system32\" & sCmd

sReturnNTP = fShellRun(sCmd)   'good return value, same as manual cmd line
Debug.Print sReturnNTP
                
sCmd = "ntpq -p"
sCmd = "%ComSpec% /C %SystemRoot%\system32\" & sCmd
                
sReturnNTP = fShellRun(sCmd)   'ERROR return value, even though manual cmd line has good values
Debug.Print sReturnNTP

End Sub

Public Function fShellRun(sCommandStringToExecute) As String

' This function will accept a string as a DOS command to execute.
' It will then execute the command in a shell, and capture the output into a file.
' That file is then read in and its contents are returned as the value the function returns.

' "myIP" is a user-selected global variable

Dim oShellObject, oFileSystemObject, sShellRndTmpFile
Dim oShellOutputFileToRead
Dim iErr As Long

Set oShellObject = CreateObject("Wscript.Shell")
Set oFileSystemObject = CreateObject("Scripting.FileSystemObject")

    sShellRndTmpFile = oShellObject.ExpandEnvironmentStrings("%temp%") & oFileSystemObject.GetTempName
    On Error Resume Next
    oShellObject.Run sCommandStringToExecute & " > " & sShellRndTmpFile, 0, True
    iErr = Err.Number

    On Error GoTo 0
    If iErr <> 0 Then
        fShellRun = "error"
        Exit Function
    End If

    On Error GoTo err_skip
    fShellRun = oFileSystemObject.OpenTextFile(sShellRndTmpFile, 1).ReadAll
    oFileSystemObject.DeleteFile sShellRndTmpFile, True

Exit Function

err_skip:
    fShellRun = "error"
    oFileSystemObject.DeleteFile sShellRndTmpFile, True

End Function

sCommand = "ntpq.exe -p"
    Set WshShell = CreateObject("WScript.Shell")
    Set WshShellExec = WshShell.Exec(sCommand)
    strOutput = WshShellExec.StdOut.ReadAll
    Debug.Print strOutput

I very much appreciate your time, KJ. Using CMD line window, both "drive:\full path to \ntpq.exe -p " and "ntpq.exe" work. Both do not work in my script. I copied the string from the script to the cmd line window to make sure no typos. — photonblaster, Oct 01 '21 at 05:27

score 0 · Answer 1 · answered Oct 01 '21 at 08:59

Your fShellRun function didn't work due to error in temporary file path. Here is fixed version.

Function fShellRun(sCommandStringToExecute) As String
...
  'invalid file path without path separator between directory path and filename!
  sShellRndTmpFile = oShellObject.ExpandEnvironmentStrings("%temp%") & _
    oFileSystemObject.GetTempName

  'valid path with path separator between directory path and filename
  sShellRndTmpFile = oFileSystemObject.BuildPath( _
    Environ("temp"), oFileSystemObject.GetTempName)
...
End Function

I appreciate you valued time, "artnib". I made the changes you suggested, but I did not see a change in the path string. And still does not work. I did some additional debugging based on comments here and further research, see edited question above. — photonblaster, Oct 02 '21 at 04:35

manual entry in cmd window works, VBA executing CMD works, but not VBA when I use run (so I can hide the window)

1 Answers1