CODEWITHSUNDEEP
javaexcelexcel-formulaexcel-2003jxl
Nimit_ZZ
Nimit_ZZ

Reputation: 495

Microsoft Excel Macro to run Java program

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?

Upvotes: 12

Views: 52552

Answers (4)

Margus
Margus

Reputation: 20038

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.

Upvotes: 24

Tony Roberts
Tony Roberts

Reputation: 417

Much better than the other suggested solutions is to create an Excel Add-In in Java using Jinx (https://exceljava.com).

See https://exceljava.com/docs/macros.html for details of how to write Excel macros in Java.

As well as macros, it is also possible to write user defined functions and menus.

In fact, you can use Java as a full replacement for VBA! See https://github.com/exceljava/jinx-com4j for how to call the Excel Object Model to allow you to do everything you can in VBA from Java.

Upvotes: 0

mschwehl
mschwehl

Reputation: 31

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

Upvotes: 3

Sreehari Puliyakkot
Sreehari Puliyakkot

Reputation: 736

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(...).

Upvotes: 3

Related Questions