VBA调用Jar获取StdOut失败求助（涉及Excel、WSH）

Hey there! I’ve dealt with this exact headache before—VBA + WSH + Java jars can be tricky when it comes to capturing stdout. Let’s break down what’s probably going wrong and how to fix it.

First, let’s start with a revised VBA code snippet that addresses the most common issues, then walk through why each part matters:

Sub RunJarAndGetOutput()
    Dim wshShell As Object
    Dim execProcess As Object
    Dim javaExePath As String
    Dim jarFilePath As String
    Dim runCommand As String
    
    ' Update these paths to match your system!
    javaExePath = "C:\Program Files\Java\jdk1.8.0_301\bin\java.exe"
    jarFilePath = "C:\YourProjectFolder\yourResultJar.jar"
    
    ' Build command with quoted paths (critical for spaces in paths)
    runCommand = """" & javaExePath & """ -jar """ & jarFilePath & """"
    
    ' Initialize WSH shell
    Set wshShell = CreateObject("WScript.Shell")
    ' Use Exec (not Run!) to capture stdout/stderr
    Set execProcess = wshShell.Exec(runCommand)
    
    ' Wait for the jar to finish executing (it takes ~4 seconds)
    Do While execProcess.Status = 0
        DoEvents ' Prevent Excel from freezing
        Application.Wait Now + TimeValue("00:00:01") ' Check every second
    Loop
    
    ' Read both stdout AND stderr (stderr is often the key to hidden errors)
    Dim outputResult As String
    Dim errorLog As String
    outputResult = execProcess.StdOut.ReadAll
    errorLog = execProcess.StdErr.ReadAll
    
    ' Print results to the Immediate Window (Ctrl+G in VBA editor)
    Debug.Print "Captured Output: " & outputResult
    Debug.Print "Error Log (if any): " & errorLog
    
    ' Cleanup objects
    Set execProcess = Nothing
    Set wshShell = Nothing
End Sub

Key Issues & Fixes #

1. Use Exec Instead of Run #

WSH’s Run method only executes a command—it can’t capture stdout or stderr. The Exec method returns a WshScriptExec object that gives you direct access to both output streams. If your original code used Run, that’s why you got nothing.

2. Wait for the Jar to Finish Before Reading Output #

Your jar takes ~4 seconds to run. If you call StdOut.ReadAll immediately after starting the process, the jar hasn’t had time to write its output yet. The Do While loop waits for the process status to change from 0 (running) to 1 (completed), ensuring you only read output once it’s ready.

3. Fix Java’s Output Buffering Problem #

Java’s System.out uses a buffer that doesn’t always flush automatically when outputting to non-interactive environments (like WSH’s pipe). This means your System.out.println("1000") might be stuck in the buffer instead of being sent to stdout. You have two options here:

• Modify your jar code: Add System.out.flush(); right after your println statement to force the buffer to empty.
• Command line tweak: Add the -Djava.io.tty=false flag to your Java command to disable terminal-specific buffering:

  runCommand = """" & javaExePath & """ -Djava.io.tty=false -jar """ & jarFilePath & """"
  

4. Quote Your Paths #

If your Java or jar path has spaces (like Program Files), you need to wrap them in double quotes. Without quotes, WSH will split the path into separate command arguments, causing the process to fail silently.

5. Always Check StdErr #

Even if stdout is empty, stderr might have critical error messages (e.g., "Java not found", "Invalid jar file", "Class not found"). Reading StdErr.ReadAll will help you diagnose hidden issues that aren’t visible when running manually (like environment variable differences between your user session and Excel’s VBA session).

内容的提问来源于stack exchange，提问作者Daniel Gontijo Lopes