4

I need to generate an Excel sheet from the data fetched from a database through Java. For that, I need to call some VBA Macro functions while generating that Excel. Can anybody help me with how to call VBA Macro from Java code?

Gaffi
  • 4,307
  • 8
  • 43
  • 73
Parth Trivedi
  • 173
  • 2
  • 2
  • 7

5 Answers5

7

If you can't use JACOB or COM4J you can make a Visual Basic Script and run the script from your Java program.

To create the script open notepad and write something like this:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("myExcel.xlsm")

objExcel.Application.Run "myExcel.xlsm!MyMacroName" 
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit
WScript.Quit

Save it as myVBS.vbs and you can call it like this from your Java code:

cmd = "you_path\\myVBS.vbs";
Runtime.getRuntime().exec(cmd);
Nathaniel Ford
  • 20,545
  • 20
  • 91
  • 102
Luis Herrería
  • 101
  • 1
  • 6
7

I don't really understand your overall approach to generate Excel sheet from the data in a database. Normally, I'd use Apache POI as proposed by Vivek.

However, if you really need to call an Excel macro in a sheet, then you need two things:

First, you need a JAVA-to-COM bridge like JACOB, COM4J or a similar tool. It is sufficient if it supports automation interfaces. It doesn't need to have full COM support.

Second, using the JAVA-to-COM bridge, you should start Excel, load the Excel sheet, run the macro, save it and close Excel. So you have to call the equivalent of:

Set Wb = Application.Workbooks.Open FileName
Application.Run MacroName
Wb.Save
Application.Quit
Codo
  • 75,595
  • 17
  • 168
  • 206
1

solution that works for me: java code:

try {
Runtime.getRuntime().exec("wscript yourPth\\myVBS.vbs");
} catch (IOException e) {
System.out.println(e);
System.exit(0);
}

myVBS.vbs script:

Set objShell = CreateObject("WScript.Shell")
Dim cur
cur = "urpath to myVBS.vbs script"
WScript.Echo cur

ExcelMacroExample

Sub ExcelMacroExample() 

Dim xlApp 
Dim xlBook 
Dim xlsFile
xlsFile = cur & "\myExcel.xlsm"

Set xlApp = CreateObject("Excel.Application") 
Set xlBook = xlApp.Workbooks.Open(xlsFile) 
xlApp.Run "moduleName"
xlApp.Save
xlApp.Quit 

End Sub
Rahul k
  • 69
  • 3
1

You might also be able to capture an event in excel when a worksheet changes you can have the even call whatever macro you want it to, so if you want to call the macro "Blue" you can write "blue" in a hidden worksheet then Excel will capture the change, when you capture the change you can see what was written and do some if ifelse statements to get to the macro you want to call for that instance. Not really good coding, but an easy workaround. I would do the other methods myself though.

Jon49
  • 4,444
  • 4
  • 36
  • 73
  • Thanks for the answer Jon, we can do that way also. but it is a tedious one. And currently we have around 10 sheets in a workbook that are full of macros. So inserting a new macro and maintaining it is a tough job. – Parth Trivedi Oct 18 '11 at 12:37
  • It wouldn't be the way I would do it but it would work, I would set up an event handler class so you wouldn't have to put it on each worksheet. – Jon49 Oct 18 '11 at 18:21
  • Event Handler 'class' in Macro ?? How to do that ?? – Parth Trivedi Oct 19 '11 at 04:36
  • http://www.cpearson.com/excel/Events.aspx (general overview of events) and http://www.cpearson.com/Excel/AppEvent.aspx (specific app events) – Jon49 Oct 19 '11 at 04:55
-1

I am not sure if it is possible to call macro directly from Java. But you can populate the data in excel sheet & call the macro when the user opens the excel sheet for the first time. You would be able to populate data in a excel sheet containing macros using Apache POI tool - http://poi.apache.org/spreadsheet/index.html

Vivek Viswanathan
  • 1,968
  • 18
  • 26
  • Thanks for the response sir. Actually we have an excel template already designed. I want to fill that template using java. While filling the template, we need to call some macro functions to manipulate written data. We are currently using JExcel to read excel. But we don't know that a macro can be called from it. So Do you know that jxl can be used for this purpose ? – Parth Trivedi Oct 17 '11 at 07:02
  • JExcel is similar to Apache POI. It's a platform independent library for reading and writing Excel files without using Excel itself. You cannot run a macro with it. But JExcel is able to open an existing Excel file (your template), add data to it and do all sorts of formatting. So I propose you implemtn with JExcel what the macro would do. If you need to run it on a non-Windows platform or in a server environment (e.g. a web application), you cannot run Excel macros anyway because Excel only supports Windows and only single user environments. – Codo Oct 17 '11 at 07:29
  • Thanks Codo, We are currently using JExcel and we also think that we should go with it instead of introducing a new API to the project. And Yes, we will do the required coding that is required as we are still not able to find how to call a Macro from java. So we are going this way only to format and change related things by JExcel. Thank you once again. – Parth Trivedi Oct 18 '11 at 12:41