JInvokeVBA Example

Contents

  1. Introduction
  2. Prerequisites
  3. Create an Excel Workbook with VBA Code
  4. Run the Java Client on Local Windows Machine
    1. Generate Java Proxies
    2. Create the Example
    3. Compile and Run the example
  5. Run the Java Client on Remote Machine, e.g. Windows, UNIX, Linux and etc
  6. More about Excel programming

1 Introduction

This example shows you how to use J-Integra® to invoke existing VBA (Visual Basic for Application) objects in a Microsoft Excel workbook with pure Java.

You will use J-Integra® to interact with the objects, properties and methods exposed by Microsoft Excel either locally or remotely.

VBA (Visual Basic For Applications) is a macro language that is identical to the Visual Basic programming language. By using VBA you can easily handle objects and collections exposed by Microsoft Office. Since VBA can be embedded inside Excel workbook, you can use J-Integra® to trigger a VBA macro by emulating the end user’s interaction.

You can run the Java client on a Windows machine to access its local Microsoft Excel, or run the Java client on a non-Windows machine (such as Linux) to access Microsoft Excel installed on a remote Windows machine.

2 Prerequisites

  1. Install Microsoft Office (Microsoft Office 2003 is recommended)

  2. Download and install the Java™ Developers Kit

  3. Download and install J-Integra®

  4. Ensure that you have you have installed the J-Integra® license.

3 Create an Excel Workbook with VBA Code

This document is based on Microsoft Excel 2000 API. If you are using Microsoft Excel 97 or Excel XP, please note that Excel Programming APIs are different among Excel 97, 2000 & XP, and therefore you need to use different method calls of generated Java proxies.

  1. Create an empty Excel workbook in C:\Java-VBA\ and name it JInvokeVBA.xls.

  2. In Excel, click Tools > Macro > Visual Basic Editor

  3. The Visual Basic Editor opens. In the Project window, double click Sheet1, copy and paste the following VBA code: 

    Private Sub Worksheet_Activate()
    Range("B2:B14").Value = 10000
    Range("B15").Formula = "=Sum(B2:B14)"
    Range("B15").Font.Bold  = True
    Range("B15").Font.Color = RGB (0, 255, 0)
End Sub

Public Function method1(ByVal p1 As String, ByVal p2 As Date) As Double
    Range("C2").Value = p1 & " " & p2
    method1 = 1234.5678
End Functions
  4. Right click on Microsoft Excel Objects, select insert, then select Module, copy and paste the following VBA code: 

    Public Sub test()
    MsgBox "Sub test() in Module1 is invoked"
End Sub
  5. Now your VBA code should be like this.

  6. Compile the VBA code and save the file.

4 Run the Java Client on Local Windows Machine

4.1 Generate the Java proxies

Run J-Integra®'s com2java tool on the Windows machine, and select C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE as the type library, choose an empty directory named excel (e.g. C:\excel) as output directly, and use excel as Java package name. Click the Generate Proxies button to generate Java proxies from Excel type library.

4.2 Create the example

This example is for Microsoft Office Excel 2003. If you use a different version of Excel, you need to look at the generated Java proxies (Workbooks.java) and call the methods accordingly.

Create a .java file named JInvokeVBA.java. Then copy and paste the Java code below:

 
import excel.*;
import java.io.*;
import java.util.*;

public class JInvokeVBA {

  public JInvokeVBA() {
  }

  public static void main(String[] args) {

    try {
      JInvokeVBA myInstance = new JInvokeVBA();

      // DCOM authentication: Make sure NT Domain, NT User, NT Password are valid credentials.
      // Uncomment this line if JInvokeVBA.java remotely accesses Excel:
      // com.linar.jintegra.AuthInfo.setDefault("NT DOMAIN", "NT USER", "NT PASSWORD"); 

      String strExcelFilename = "C:\\Java-VBA";
      try {
        File f = new File("");
        strExcelFilename = f.getAbsolutePath();
      } catch (Exception ex) {
        ex.printStackTrace();
      }
      strExcelFilename += "\\JInvokeVBA.xls";

      myInstance.testVBA(
        strExcelFilename,
        "Sheet1",
        true,
        false
      );
    } catch(Exception e) {
      e.printStackTrace();
    } finally {
      // Release all remote objects that haven't already been garbage collected.
      com.linar.jintegra.Cleaner.releaseAll();
    }
  }

  public void testVBA(
    String strWorkbookFilePath,
    String strWorksheetName,
    boolean bExcelAppVisible,
    boolean bExcelAppDisplayAlerts) {
    try {
      Application app = null;
      Workbooks workbooks = null;
      Workbook wb = null;
      int iTemp = 0;
      Integer iTemp2 = null;
      Sheets worksheets = null;
      Worksheet ws = null;
      String strTemp = null;
      Range rg = null;

      // Specify host name or IP address of Excel machine as parameter if 
      // JInvokeVBA.java remotely accesses Excel.
      // app = new Application("123.456.789.0"); 
      app = new Application();

      app.setVisible( bExcelAppVisible );
      app.setDisplayAlerts( bExcelAppDisplayAlerts );

      // open existing workbook
      workbooks = app.getWorkbooks();

      // Open the excel file
      // For Excel XP or 2003, use:
      wb = workbooks.open(
        strWorkbookFilePath,
        new Boolean(true),
        new Boolean(true),
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null
      ); 
      // For Excel 2000, use:
      // wb = workbooks.open(
      //   strWorkbookFilePath,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null
      // );

      // Operating Worksheets and Worksheet objects
      worksheets = wb.getWorksheets();

      iTemp = worksheets.getCount();

      while(iTemp > 0) {
        ws = new Worksheet(worksheets.getItem(new Integer(iTemp)));
        strTemp = ws.getName();
        if( strWorksheetName.equals(strTemp) ) {
          break;
        }
        iTemp--;
      }

      // Invoke the method "method1" 
      Object[] params = { "hello", new java.util.Date() };
      Double retVal = (Double) ws.invokeMethodByName("method1", params);
      System.out.println("VBA method1 returns: " + retVal);

      app.run(
        "test",
        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
      );

      Thread.sleep(5000);
      wb.close(new Boolean(true), null, null);
      workbooks.close();
      app.quit();
    } catch( Exception e ) {
      e.printStackTrace();
    }
  }
}

4.3 Compile and run the example

On the Java client machine, make sure your CLASSPATH and PATH environment variables are set up according to J-Integra® installation instructions. Compile and run the example in  J-Integra®'s native mode (you need to use DCOM mode if remotely accessing Excel):

The -J parameters make more memory available to the compiler than it would normally have, since there are a lot of generated files in this example.

javac -J-mx32m -J-ms32m JInvokeVBA.java
java -DJINTEGRA_NATIVE_MODE JInvokeVBA

JInvokeVBA will open JInvokeVBA.xls, activate Sheet2, and run the VBA macro.

5 Run the Java Client on Remote Machine, e.g. Windows, UNIX, Linux and etc

You can also run the Java client on a remote machine, such as Linux, Solaris, UNIX and AIX. For instance, if you run it on a Linux machine, then you must do the following.:

  1. Move the com2java tool to the Windows machine to generate the Java proxies from Excel, and then move the Java proxies from the Windows machine to the Linux machine.
  2. Install J-Integra® (the jintegra.jar file) on the Linux machine and include the jintegra.jar file and generated Java proxies in CLASSPATH.
  3. Use DCOMCNFG to configure Excel on the Windows machine.
  4. Pass correct login credentials to com.linar.jintegra.AuthInfo.setDefault("NT DOMAIN", "NT USER", "NT PASSWORD");
    Refer to Configuring DCOM for Remote Access for more information about AuthInfo.setDefault.
  5. Pass the IP address or computer name of the Excel machine to the constructor of Applicaiton object in JInvokeVBA.java:
    Application doc = new Application("123.456.789.0");
  6. Move JInvokeVBA.java to the Linux machine. Compile and run it in DCOM mode without using DJINTEGRA_NATIVE_MODE property:
    java JInvokeVBA

6 More about Excel programming

To access OLE Programmatic Identifier (ProgID) from Java, you'll need to generated Java proxies from C:\WINDOWS\system32\FM20.DLL using com2java and put them in a Java package named "FM20". For example, open JInvokeVBA.xls, click View -> Toolbars -> Control Toolbox, and draw a Command Button named "CommandButton1" on the Sheet1. To control this Command Button from Java, you'll need to use this code snippet below.

Worksheet ws = new Worksheet(worksheets.getItem("Sheet1");
FM20.CommandButton commandButton = new FM20.CommandButton(ws.getPropertyByName("CommandButton1"));
commandButton.setValue(true);

J-Integra® is a generic bi-directional bridge between any Java objects and any COM components. We do not provide the documentation of the generated Java proxies since the Java proxies are just mapped from the programming API of the COM component. For more information about Microsoft Excel programming, please refer to the Microsoft Excel VBA Language Reference. It's also easier to find a VB example first, and then convert the VB example to Java program using the reference Mapping VB Code to Java Code.