How to Call an Excel Vba Macro from Java Code

How to call an Excel VBA Macro from Java Code?

I don't really understand your overall approach to generate Excel sheet from the data in a database. Normally, I'd use Apache POI as proposed by Vivek.

However, if you really need to call an Excel macro in a sheet, then you need two things:

First, you need a JAVA-to-COM bridge like JACOB, COM4J or a similar tool. It is sufficient if it supports automation interfaces. It doesn't need to have full COM support.

Second, using the JAVA-to-COM bridge, you should start Excel, load the Excel sheet, run the macro, save it and close Excel. So you have to call the equivalent of:

Set Wb = Application.Workbooks.Open FileName
Application.Run MacroName
Wb.Save
Application.Quit

Unable to run macro from java code

To run a vbs script, you need to run Wscript.exe and pass the vbs file as a parameter.

For example,

Runtime.getRuntime().exec(new String[]{"C:\\Windows\\System32\\wscript.exe", "D:\\myVBS.vbs"});

How do I call VBA code in an Excel spreadsheet from Java?

I basically see three options for calling VBA code in Excel from a Java application:

1. Java COM Bridge: There are several tools available that allow you to call COM or COM Automation components from Java. Excel is such a component. I know of Jacob and JCom, but there might more such tools available.

2. Java / VBScript / COM Automation: Since you obviously don't need to pass data to the VBA code, the simplest solution is probably to write a VBScript that starts Excel, opens the document, calls the macro and closes Excel. This script is started from Java with Runtime.getRuntime().exec("cmd /c start script.vbs");

3. JNI: You could write a specific DLL for your applications. It implements the JNI interface so it can be called from Java. And its implementation uses COM calls to work with Excel. Such a DLL is best written with VisualStudio and it's support for C++ to call COM objects.

Whatever your solution will be, you basically want to execute the following commands on Excel automation interface (sample in VBScript):

Dim xl
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("workbook.xlsx")
xl.Application.Run "MyMacro"
xl.Application.Quit
Set xl = Nothing 

You cannot compile VBA code into a DLL. There exists no tool for that (in contrast to the full Visual Basic).

I hope this answer is helpful even though I didn't understand what you mean by: "we want to assume that the user of the Java application does not necessarily have immediate access to Excel, but is operating on a Windows machine."

Call a VB macro from java code

You can run vbscript using the "cscript.exe" that ships with windows.

Depending upon your scenario, you can launch this from Java in a variety of ways:

• use Runtime.exec to launch the program. You can do this directly as part of your program.
• use Ant, which has an exec task, or maven which has an exec plugin. This is most useful when invoking the script as part of a build or some other batch process.

EDIT: If your script has a GUI, then use "wscript.exe".

I'm assuming you mean vbscript, but if you reall mean a macro, such as a Word macro, then you will need to do something like this:

"C:\Program Files\Microsoft Office\Office12\Winword.exe" 
"C:\MyPath\MyDoc.doc" /m"Macro1"

Alternatively, you can create a small vbscript that instantiates the Word Application and uses the run() method to invoke a macro. You execute that script using cscript.exe/wscript.exe.

Microsoft Excel Macro to run Java program

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.

---

Related Topics