How to call an Excel VBA Macro from Java Code?

Question

I need to generate an Excel sheet from the data fetched from a database through Java. For that, I need to call some VBA Macro functions while generating that Excel. Can anybody help me with how to call VBA Macro from Java code?

Answer 1

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

Answer 2

If you can't use JACOB or COM4J you can make a Visual Basic Script and run the script from your Java program.

To create the script open notepad and write something like this:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("myExcel.xlsm")

objExcel.Application.Run "myExcel.xlsm!MyMacroName" 
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit
WScript.Quit

Save it as myVBS.vbs and you can call it like this from your Java code:

cmd = "you_path\\myVBS.vbs";
Runtime.getRuntime().exec(cmd);