Call VBA code with Java

Question

Greetings StackOverflow,

I searched the great Internet and was saddled with numerous waste messages or answers that explain the exact opposite order of what I'm trying.

I have an Excel file with a large set of VBA code. There are 4 publicly available routines that do not accept any parameters that can be called by the user when opening a document in Excel, while they process data in various sheets as needed. We have a great Java application with which we would like to interact with this document by invoking Macros from the Java environment. The thing is, we only need to write VBA code once, and then Java can call it to execute. In addition, we want to assume that the user of the Java application does not necessarily have immediate access to Excel, but works on a computer running Windows. How should this be done?

Putting VBA code in a DLL and calling it from Java? How do you compile a DLL, what is the use of Visual Studio for? What do we call DLLs with Java? Should we try some kind of COM object?

Please note that as the most common answer to my question elsewhere, I quite clearly understand how to call Java from VBA, but not to call VBA from Java.

Thanks in advance.

Answer 1

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

• 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 Jacob and JCom , but such tools may be available there.

• Automation Java / VBScript / COM . Since you clearly do not need to pass data to VBA code, the simplest solution is probably a VBScript entry that launches Excel, opens a document, calls a macro, and closes Excel. This script is launched from Java using Runtime.getRuntime().exec("cmd /c start script.vbs");

• JNI . You can 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 using VisualStudio and supports C ++ for calling COM objects.

Regardless of your solution, you basically want to execute the following commands in the 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 in a DLL. There is no tool for this (as opposed to the full Visual Basic).

I hope this answer will be useful, although I do not understand what you are saying: "We want to assume that the user of the Java application does not necessarily have immediate access to Excel, but it works on a computer running Windows."