Macro does not execute when attached to exporter using API

8

We are using Jasper Reports API version 4.7.1 in Java. We are trying to attach a Macro to our report with the XLSM extension, however when opening the file the macro is not executed. This is the following line used to attach the macro to our exporter:

exporter.setMacroTemplate(xlsTemplateFilePath)

Edit: I should mention we are using the JRXlsxExporter class, and exporting to .xlsm type.

The following is the VB script in the Macro that is attached to our report:

Option Explicit
Private Sub Workbook_Open()
MsgBox "Hello"
End Sub

The file opens fine in Excel, and I am able to see the above VB script (ALT + F11), however when I hit on 'Enable Content' after opening the file the Macro code is not ran. In the above case a pop-up window should open with the word "Hello". If we are to attach the code manually in Excel and open the file, it does indeed work, but not when using the Jasper Report API.

Any help to get this to work would be greatly appreciated. Please let me know if theres anything else I can add.

Thank you!

jamoros's picture
42
Joined: Aug 15 2016 - 4:07pm
Last seen: 7 months 5 days ago

This is happening to me as well. The Workbook object containing the Workbook_Open() appears in the VBA project but the Workbook_Open() is never ran. After double-clicking the Workbook object from the VBA editor the Workbook object seems to duplicate and a blank Workbook_Open() is in that one. It appears the added Workbook object with the Workbook_Open() code is not the real Workbook object. Very strange behavior. Is there a fix for this?

derylspielman_1 - 2 years 8 months ago

First of all look into http://community.jaspersoft.com/jasperreports-library/issues/2363#commen... as apparently Jasper Reports provided the solution I suggested years ago.

 

If that does not work then I assume XLS macros do still load correctly (for example following  http://thinkinginsoftware.blogspot.com/2011/11/excel-jasper-reports-with...). Similar code should be available for XLSX.

 

I would create a java standalone program (containing main method) that would load the original xlsm, add some text in A1 and then save it with POI could help in understanding if there is an issue with Apache POI. We should manually open the saved file and expect it still opens the message box just like the original xlsm. If it does not then we need to open a bug with Apache POI project. If it does then we add that information here and potentially create a bug ticket for Jasper Reports API.

 

In addition posting the question to stack overflow just as you did over a year ago (http://stackoverflow.com/questions/28593223/apache-poi-opcpackage-unable...) could help as well.

 

Best regards,

 

- Nestor

nestoru - 2 years 8 months ago

0 Answers:

No answers yet
Feedback
randomness