Get the Output of a Java String From VBA

Question

I need to get the output string of a java function into my VBA project. As a fast example, I am trying to get the output of the java version installed but in the real application it will be other private functions.

First attempt:

' Needs a reference to Windows Script Host Object Model
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub get_java_output()
    Dim cmd_windows    As New WshShell
    Dim execution_cmd  As WshExec
    Dim command_str    As String
    command_str = "java -version"
    Set execution_cmd = cmd_windows.exec("cmd.exe /c " & command_str)
    Do While execution_cmd.Status = WshRunning
        Sleep 20
    Loop
    final_string = execution_cmd.StdOut.ReadAll
    Debug.Print final_string
End Sub

Second attempt:

Sub get_java_output_2()
    Dim windows_shell As Object
    
    Set windows_shell = CreateObject("WScript.Shell")
    command_str = "java -version"
    shell_output = windows_shell.Run("cmd /c " & command_str & " > c:\temp\output.txt", 0, False)
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set File = fso.OpenTextFile("c:\temp\output.txt", 1)
    final_string = File.ReadAll
    File.Close
    Debug.Print final_string
End Sub

None of them worked for me.

I would like to avoid the use of temporary files as in my second attempt example. In the final usage, I will call this function hundred of thousand of times, and I prefer not to create that amount of files or edit that file so many times...

This isn't really related to Java per se. You are trying to capture the output of a process invoked via a shell command. Perhaps one of the answers here might help you: https://stackoverflow.com/questions/2784367/capture-output-value-from-a-shell-command-in-vba — Hulk, Nov 25 '20 at 13:33

score 0 · Answer 1 · answered Nov 26 '20 at 07:41

I've found the solution for boths attempts and basically it is the same problem: the output of a java function displayed on the command shell is treated as an error, therefore it is not an output. The doubt was solved here

In the case of sending the java output to a file, it is solved using 2> instead of just > as it is said here. Anyway, there are a lot of different variants depending of what you need, as reported in this other link. A minor problem of this method is that the creation of the output file needs a time to be done, therefore the final variable of the Run command must be settled to True

And the code is as follows:

Sub get_java_output_2()
    Dim windows_shell As Object
    
    Set windows_shell = CreateObject("WScript.Shell")
    command_str = "java -version"
    shell_output = windows_shell.Run("cmd /c " & command_str & " 2> c:\temp\output.txt", 0, True)
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set File = fso.OpenTextFile("c:\temp\output.txt", 1)
    final_string = File.ReadAll
    File.Close
    Debug.Print final_string
End Sub

In the case of taking directly the java output to a variable, it is solved using StdErr instead of StdOut. I got the illumination reading this link. The WshExec object has three main elements: StdErr, StdIn and StdOut. If java output string is treated as an error, it should be inside StdErr instead.

And the code is as follows:

' Needs a reference to Windows Script Host Object Model
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub get_java_output()
    Dim cmd_windows    As New WshShell
    Dim execution_cmd  As WshExec
    Dim command_str    As String
    command_str = "java -version"
    Set execution_cmd = cmd_windows.exec(command_str)
    Do While execution_cmd.Status = WshRunning
        Sleep 20
    Loop
    final_string = execution_cmd.StdErr.ReadAll
    Debug.Print final_string
End Sub

Get the Output of a Java String From VBA

1 Answers1