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?

Regards,
Jenty
rospccAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

suprapto45Commented:
Hi,

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:
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 :(

Regards,
Jenty
0
suprapto45Commented:
Hi Jenty,

>>"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.

What's your Macro doing in the Excel file?

David
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

objectsCommented:
Would the VB macro work on a Linux platform?

You could still use JNI on linux you would just use a shared lib instead of a dll.
0
rospccAuthor Commented:
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 :(


Regards,
Jenty
0
suprapto45Commented:
>>"Would the VB macro work on a Linux platform?"
I do not know whether Open Office Macro will work in Linux or not.

David
0
rospccAuthor Commented:
As for now, I can afford to focus on getting it work on Microsoft Platform first. Do you have any solution?


Regards,
rospcc
0
suprapto45Commented:
>>"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"

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.

David
0
objectsCommented:
i'd first try using jni
0
rospccAuthor Commented:
>>> 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?
0
suprapto45Commented:
>>"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

David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
suprapto45Commented:
By that, you do not need to worry that you need to manually open and close the Excel file as everytime the user opens the Excel file, the Macro will run automatically.

Bear in mind that it may not work in Linux / OpenOffice.

David
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.