COURSE of the MONTH

How to execute excel macro from Java?

Hi,

I have an excel document containing macro, and I want to execute this macro from Java. Otherwise, now I have to manually open the excel document and run the macro. Does anybody know how can I do this from Java?

Who is Participating?

>>"are you saying that I can activate the macro automatically when users open up the file?"
yes definitely (if I am not mistaken). We are Java developer ;) but yes, you can set the codes in the Worksheet_Activate sub function

As far as I know, you can't. I do not know about other experts' opinion but I do not think that it is possible. Macro is developed in VB-style programming (VBA) thus I do not think that we can easily call it.

There are few alternatives,
First, try to create a DLL file that call this Macro (create the DLL from C++ or VB - VB definitely can call Macro) and call this DLL from JNI. However, I am not sure whether it is the right way or not. Secondly, you have to re-write the Macro into Java programming. I personally prefer the second solution.

Yes, that is pain ;)

David

0

rospccAuthor Commented: 2006-04-06

Hi,

I used JNI previously to extract Windows information by calling DLL. I think in my case, it would be easier for me to implement your first solution :)

Secondly, the macro is already created in VB. If it is easy to execute a macro from DLL, again solution one is easire :)
The disadvantage for me to use solution 1 is multi-platform. I developed the application in Java because we need it to be multiplatform. If the client decided to use LINUX, the DLL method won't work. Currently we generate the excel using Open Office.

Could you give me some information on how to re-write macro into java programming? It sounds difficult :(

>>"Could you give me some information on how to re-write macro into java programming?"
What I mean here is that you need to get rid the use of Macro in the Excel. If it is just a calculation/updating cell macro, it would be pretty easy to modify it into Java (using POI). However, if it is more complex Macro, it would be harder.

You could still use JNI on linux you would just use a shared lib instead of a dll.

0

rospccAuthor Commented: 2006-04-06

I am not familiar with Linux though :(

To be more details, We developed our application in JSP and wrote the data into Open Office Calc document in order to display dynamic charts. We tried other APIs that link Java to Ms. Excel, but some cannot create dynamic charts, some were expensive and tedious in codings. So, using the OpenOffice API is quite good.

But in Open Office itself, it has a bug on the charts generated. Let say we generate a bar chart containing 10 rows of data, which means 10 bars shown on the chart. When we display the Data Label, the 1st bar would never had any value. After generated the report, we will save in .xls format because most clients PCs are Windows based.

Here are the steps:
1. We created an .xls document as a template with a macro built-in. The macro is basically to show all the data labels. When we execute the data labels from Excel, it will show every value on every bar.
2. We use Java and Open Office API to open the document, write whatever information we need and generate the chart.
3. Save the .xls file after writing
4. This is the step that is being done manually now. We have to manual open up each report generated, and click Macro -> Run in order to show all the value on each bar.

So, we need to get rid of that manual step as it is very tedious when we need to open up all the reports just to run the macro, and we always have hundreds of reports generated every time :(

>>> Why don't you make the Macro to run everytime the user opens the Excel file? However, you need to use VBA in this case

Currently I run the macro when the we opens the file, then we save, but this is manual and very tedious to open hundreds of reports just to run the macro. Based on what you mentioned, are you saying that I can activate the macro automatically when users open up the file?