Microsoft Excel Macro to run Java program

Question

I have learnt to read and write an Excel file using a Java program with the help of Jxl and POI API. Is it possible to run a Java program with the help of macros?

Solution 1

Yes, it is possible.

There are quite a few ways actually and I hope you like my examples.

To demonstrate this, I create a program where some text is send as arguments and program responds with an altered version of it. I made a runnable jar of it. First example reads the argument from args and other from standard input.

File Hello.java and H1.jar:

public class Hello {
    public static void main(String[] args) {
        StringBuilder sb = new StringBuilder("Hello");
        
        if (args.length > 0) 
            sb.append(' ').append(args[0]);
        System.out.println(sb.append('.').toString());
    }
}

File Hello2.java and H2.jar:

import java.util.Scanner;

public class Hello2 {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        StringBuilder sb = new StringBuilder("Hello");
        
        sb.append(' ').append(sc.nextLine());
        System.out.println(sb.append('.').toString());
    }
}

You can save them in a single jar, but then you need create and use a manifest (that's a bit overkill).

Now in Excel I add a module and a reference to Windows Script Host Object. If you do not like the sleep, then you can replace it with DoEvents:

'add a reference to Windows Script Host Object Model
'for example : Tools-References
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub RunSleep( _
    exec As WshExec, _
    Optional timeSegment As Long = 20 _
)
    Do While exec.Status = WshRunning
        Sleep timeSegment
    Loop
End Sub

Private Function RunProgram( _
    program As String, _
    Optional command As String = "" _
) As WshExec
    Dim wsh As New WshShell
    Dim exec As WshExec

    Set exec = wsh.exec(program)
    Call exec.StdIn.WriteLine(command)
    Call RunSleep(exec)
    Set RunProgram = exec
End Function

And to test it I saved the files to c:\ drive and used the code:

Public Sub Run()
    Dim program As WshExec
    Set program = RunProgram("java -jar ""C:\\H1.jar"" Margus")
    Debug.Print "STDOUT: " & program.StdOut.ReadAll

    Set program = RunProgram("java -jar ""C:\\H2.jar", "Margus")
    Debug.Print "STDOUT: " & program.StdOut.ReadAll
End Sub

In my case I get a responce of :

STDOUT: Hello Margus.

STDOUT: Hello Margus.

Solution 2

Your VBA can write the output to a file and Java can poll for file modifications periodically and read from the file. And write the data back to VBA through another file. VBA - Java integration is next to impossible unless you just want to fire a Java program from the shell through System.execute(...).

Solution 3

i've used it.. Attention, use javaw otherwise a black window is popping up

Dim result As String
Dim commandstr As String

commandstr = "javaw -jar somejar someparameter"


' try with or without cast to string
result = CStr( shellRun(commandstr) )


'somewhere from SO but forget.. sorry for missing credits

Public Function ShellRun(sCmd As String) As String

    'Run a shell command, returning the output as a string'

    Dim oShell As Object
    Set oShell = CreateObject("WScript.Shell")

    'run command'
    Dim oExec As Object
    Dim oOutput As Object
    Set oExec = oShell.exec(sCmd)
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object'
    Dim s As String
    Dim sLine As String
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then s = s & sLine & vbCrLf
    Wend

    ShellRun = s

End Function