Windows Script Host - Unable to get StdOut from console

Question

I need a hand with a specific doubt regarding VBA, Excel and WSH. I have a excel that I want to use to run a jar. This jar takes up to 4 seconds to return a anser using System.out.println.

I created a script in VBA in excel to run this jar and to collect the results via StdOut.ReadAll, but the problem is that everytime I run it I don't get nothing as result... I'm executing the java and it is returning 1000, when I execute the vba it seems nothing happens.

This is my VBA code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Test()
Dim prog As Object
Dim Exec As Object
Dim A As String
Set prog = CreateObject("WScript.Shell")
Set Exec = prog.Exec("java -jar JExcel.jar " & Range("a2").Value) <- Im passing an argument here
    While Exec.Status = WshRunning
        Range("b2") = "Running"
        Sleep (100)
    Wend
Range("b2").Value = Exec.StdOut.ReadAll
End Sub

I read a lot of material in here and other places but got no lucky.

Does anyone knows what might be the problem?

Thank you

Does [this](https://stackoverflow.com/questions/28353522/real-time-console-output-from-wscript-shell-exec) offer any help? — QHarr, Mar 17 '18 at 10:30
Possible duplicate of [Real time console output from WScript.Shell Exec](https://stackoverflow.com/questions/28353522/real-time-console-output-from-wscript-shell-exec) — user692942, Mar 17 '18 at 16:19

score 0 · Answer 1 · answered Mar 17 '18 at 10:49

Assuming that both Java and your .jar file are not located in Excel's default save path (which you can check with VBA property Application.DefaultFilePath), you need to specify a path or two.

I'm actually unfamiliar with Java, but if your PATH evnironment variable (VBA: Environ("Path")) includes the path for the Java executable then that part should be fine, but I'd imagine you still have to specify the path for the .jar.

Also, there's a simpler way to call Shell:

Dim RetVal
RetVal = Shell("C:\WINDOWS\CALC.EXE", 1)    ' Run Calculator.

Shell Function

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

Syntax

Shell( pathname [, windowstyle ] )

_{The Shell function syntax has these named arguments:}

pathname (Required; Variant (String)) Name of the program to execute and any required arguments or command-line switches; may include directory or folder and drive.
windowstyle (Optional; Variant (Integer)) corresponding to the style of the window in which the program is to be run. If windowstyle is omitted, the program is started minimized with focus.

The windowstyle named argument has these values:

Constant          Value Description
vbHide              0   Window is hidden and focus is passed to the hidden window.
vbNormalFocus       1   Window has focus and is restored to its original size and position.
vbMinimizedFocus    2   Window is displayed as an icon with focus.
vbMaximizedFocus    3   Window is maximized with focus.
vbNormalNoFocus     4   Window is restored to its most recent size and position. 
                        The currently active window remains active.
vbMinimizedNoFocus  6   Window is displayed as an icon. 
                        The currently active window remains active.

Remarks

If the Shell function successfully executes the named file, it returns the Task ID of the started program. The Task ID is a unique number that identifies the running program. If the Shell function can't start the named program, an error occurs.

Note

By default, the Shell function runs other programs asynchronously. This means that a program started with Shell might not finish executing before the statements following the Shell function are executed.

_(Source)

Windows Script Host - Unable to get StdOut from console

1 Answers1

Shell Function