How to call a Macro from Apache POI library in Java?

Question

I have :

Macro named "process" that does all the processing work for my worksheets.

I want my Java code that uses Apache POI, to call this macro so that it can process the sheets for me.

How can I call a Macro in Apache POI in Java ?

:: Note ::

I am using import org.apache.poi.ss.usermodel in Apache POI.

Please, give sample code. (I am new to Apache POI and Java itself.)

score 11 · Accepted Answer · answered Aug 04 '11 at 07:43

11

I don't think this is possible. POI is a library to read/edit Office files, macros are a whole other beast. You would have to implement a Visual Basic for Applications Interpreter into POI to accomplish this. Macros that are already in your Excel workbook are preserved, but you cannot add new ones or call existing ones.

answered Aug 04 '11 at 07:43

Jacob

41,721
6
79
81

I already have the Macro in my file that I am editing. **I just want to execute the macro with Java POI !** – Yugal Jindle Aug 04 '11 at 08:11
1

Please note that the current version of POI has support for regular Excel functions. – parasietje Aug 02 '12 at 13:49
And if you want to execute a VBA Macro, you will either have to use OpenOffice or Microsoft Excel. You can bind to OpenOffice using their UNO interface, and to Microsoft Excel using COM (see JACOB for a COM java library). – parasietje Aug 02 '12 at 13:49

score 6 · Answer 2 · answered Sep 26 '12 at 18:25

You can use other tool like com4j. I created a POC just today and works like a charm. Just follow these steps:

Download the last version here
Using Apache Ant build the sample: samples\excel

With the code generated by tlbimp (it is just a part of com4j), create a small app and write a code like:

// Starts excel and open an existing workbook
_Application app = ClassFactory.createApplication();
app.setVisible(0, true);
_Workbook wb = app.getWorkbooks().open("c:\\test.xlsx", null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, 0);

// run a macro
Variant macro = new Variant(Variant.Type.VT_BSTR);
macro.set("my_macro");
wb.getApplication().run(macro, null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null);

// Save the excel file and quit
Variant saveBeforeExit = new Variant(Variant.Type.VT_BOOL);
saveBeforeExit.set(1);
wb.close(saveBeforeExit, Variant.getMissing(), Variant.getMissing(), 0);
app.quit();

Further examples: com4j tutorials

Is it real helpful? Does anyone confirm this? – Eugene Hoza Jul 31 '13 at 12:35 — Eugene Hoza, Jul 31 '13 at 12:35
damn, the number of null parameters gave me anxiety. – testuserx Feb 04 '19 at 10:34 — testuserx, Feb 04 '19 at 10:34

score 5 · Answer 3 · answered Aug 04 '11 at 08:01

5

I think, you can't.

Macros

Macros can not be created. However, reading and re-writing files containing macros will safely preserve the macros.

Apache POI Limitations

answered Aug 04 '11 at 08:01

ksimon

711
11
24

I already have the Macro in my file that I am editing. **I just want to execute the macro with Java POI !** – Yugal Jindle Aug 04 '11 at 08:11
19

Repeatedly stating your objective won't make it magically happen. – Ben May 19 '14 at 11:13

score 0 · Answer 4 · answered Mar 08 '21 at 10:03

One little hacky way to do this is to make your macros run automatically when the workbook is opened. This can be done from vba in ThisWorkbook module. Then you can open excel with Desktop.getDesktop().open(new File(filePath)); and with apache POI and FileInputStream just get the result of 'process' .

How to call a Macro from Apache POI library in Java?

4 Answers4

Linked