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 xlSet xl = CreateObject("Excel.Application")xl.Workbooks.Open ("workbook.xlsx")xl.Application.Run "MyMacro"xl.Application.QuitSet 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."