Excel VBA Accessing EJB

 Java/J2EE COM Interoperability Products Page

Summary

This example shows you how to use VBA from Microsoft Excel to remotely access a sample Enterprise JavaBean (EJB) hosted in a WebLogic application server, and fill out an Excel worksheet, with the support of J-Integra®.

Introduction

As a subset of Microsoft Visual Basic, VBA (Microsoft Visual Basic for applications) supports two ways to access COM objects: early binding and late binding. Late binding access means that there is no information available at compile time about the object being accessed—everything is evaluated dynamically at runtime. This means that it is not until you run the program that you find out if the methods and properties you are accessing actually exist.

Late binding is slower than early binding access, and since there is no information available about the objects being accessed, more prone to user errors. However it does provide an enormous amount of flexibility, and is easy to set up.

By using J-Integra®, Java objects, including EJBs, can be treated as COM objects in your VBA code.

This sample is a client/server application showing how to remotely access a bank account, existing on an EJB server, from a VBA client. The computer acting as the server is equipped with BEA WebLogic E-Business Platform with WebLogic Server 6.1 and Examples Server. The computer acting as the client is equipped with Microsoft Office 2000. Both the server and client have the Java Developers Kit and J-Integra® installed. The server and client can be the same computer.

Prerequisites

  1. Download and install the Java™ Developers Kit
  2. Download and install J-Integra®
  3. You will need an installation of WebLogic, including the samples that are distributed with it. Follow the instructions in the WebLogic documentation to run the Container Managed example and make sure it works. This sample will use Examples Server included in the WebLogic Server 6.1 package.
  4. Install Microsoft Office on the client computer.

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

The Steps Involved

  1. Server-side Configuration
  2. Client-side Configuration
  3. Compile and Run Java Code on the Server Side
  4. Create Excel Worksheet with VBA on the Client Side
  5. Run the Sample

Server-side Configuration

  1. This example assumes that the server has the following configuration. If the directories are different from your installation, please modify your code and configuration in the sample accordingly.

  2. If necessary, configure the environment for JDK according to the JDK documentation.

  3. Next, set up the path for the Excel JAPI. To set the PATH environment variable in the J-Integra® bin directory, type this command at the command prompt: 

    set PATH=%PATH%;C:\jintegra\bin;C:\jdk1.3.1\bin
  4. Set up the CLASSPATH by typing this command at the command prompt: 
    set CLASSPATH=%CLASSPATH%;C:\jintegra\lib\jintegra.jar;
set CLASSPATH=%CLASSPATH%;C:\bea\wlserver6.0\lib\weblogic.jar

More information on server-side configuration is available at the Intrinsyc J-Integra®® support website.

Client-side Configuration

  1. This example assumes that the client has the following configuration. If the directories are different from your installation, please modify your code and configuration in the sample accordingly.

  2. Next, set up the path for the Excel JAPI. To set the PATH environment variable in the Excel JAPI bin directory, type this command at the command prompt: 

    set PATH=%PATH%;C:\jintegra\bin
  3. Next type the following command. This tells J-Integra® that it can find the classes in the ejb JVM by connecting to TCP/IP port 7050 on the computer named samurai. 
    regjvmcmd.exe ejb samurai[7050]
    Please replace samurai with the name of the computer in which you will run the server side of this sample. If you are going to run the client and the server on the same machine, then specify localhost as the server name.

Compile and Run Java Code on the Server Side

This article is based on Excel 2000. If you are using Excel 97 or Excel XP, there may be subtle differences in the API.

  1. Create the file COMtoWebLogic.java by copying and pasting the following code from your Web browser, and save this file in jintegra\examples\java-excel\ExcelVBAToEJB directory. This file is included in your jintegra\examples\java-excel\ExcelVBAToEJB directory when you download J-Integra®. If the server and the client are the same computer, you can replace the hostname of ¡°samurai¡± with ¡°localhost¡±. 

    import javax.naming.*;
import java.util.*;
import com.linar.jintegra.*;
 
public class COMtoWebLogic {
  public static void main(String[] args) throws Exception {
    // For COM access to objects loaded via JNDI lookup
    Jvm.register("ejb", new EjbInstanciator());
    Thread.sleep(10000000);
  }
}
 
class EjbInstanciator implements Instanciator {
  Context ctx;
 
  EjbInstanciator() throws NamingException {
    Hashtable env = new Hashtable(11);
    env.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory");
    env.put(Context.PROVIDER_URL, "t3://samurai:7001");
    ctx = new InitialContext(env);
  }
 
 
  public Object instanciate(String javaClass) throws AutomationException {
    try {
      try {
        return Class.forName(javaClass).newInstance();
      } catch (Exception e) {}
      return ctx.lookup(javaClass);
    } catch (Throwable t) {
      t.printStackTrace();
      throw new AutomationException(new Exception("Unexpected: "+ t));
    }
  }  
}

  2. To compile the source code of Java sample, go to jintegra\examples\java-excel\ExcelVBAToEJB and enter the following command: 

    javac COMtoWebLogic.java

    The COMtoWebLogic.class and EjbInstanciator.class will be created in C:\jintegra\examples\java-excel\ExcelVBAToEJB directory.

Create Excel Worksheet With VBA on the Client Side

  1. On the client computer, go to C:\jintegra\examples\java-excel\ExcelVBAToEJB directory, and create an empty Excel workbook named VBA2EJB.xls. This file is included in your jintegra\examples\java-excel\ExcelVBAToEJB directory when you download J-Integra®. Open the workbook with Microsoft Office.

  2. Open the Visual Basic (VB) Editor by selecting Tools>Macro>Visual Basic Editor

  3. By default, the Excel workbook contains three worksheets named Sheet1, Sheet2, and Sheet3. Select Sheet1.

  4. Select Worksheet and Activate from the drop-down lists. Cut and paste the following code. 

    Private Sub Worksheet_Activate()
    Range("A1").Value = "Error Log"
    Range("A3").Value = "Opening Balance"
    Range("A5").Value = "Deposited"
    Range("A7").Value = "Account Balance"
    Range("A9").Value = "WidthDraw"
    Range("A11").Value = "Result"
    Range("A1:J12").Borders.LineStyle = xlContinuous
    Set home = GetObject("ejb:containerManaged.AccountHome")
    On Error Resume Next
    Set account = home.findByPrimaryKey("MyAccount")
    If Err.Number <> 0 Then
        Range("C1").Value = "Could not find Account (" & Err.Description & ")"
        Range("C2").Value = "Account being created; opening balance is $1500"
        Set account = home.Create("MyAccount", 1500, "MyAccount")
    Else
        Range("C1").Value = " none "
        Range("C3").Value = account.balance
    End If

  5. Compile the VB code and save VBA2EJB.xls.

Run The Sample

  1. On the server side, start the WebLogic Server 6.1 Examples Server. To do this, click Start>Programs>BEA WebLogic E-Business Platform>WebLogic Server 6.1>Examples>Start Example Server

  2. The following messages will be displayed in Samples Server¡¯s Command-line window.

    Note: Do not move on to the next step until the line <Started WebLogic Admin Server "exampleServer" for domain "examples" running in Development Mode> has been shown as below.

  3. After the Examples Server has started successfully, run COMtoWebLogic.class by typing the command

    java -DJINTEGRA_DCOM_PORT=7050 COMtoWebLogic

  4. On the client side, open C:\jintegra\examples\java-excel\ExcelVBAToEJB\VBA2EJB.xls. If you see a Microsoft Excel dialog box warning that Macros may contain viruses, click Enable Macros.

  5. To activate Sheet1, you must first click and activate Sheet2:

  6. Then click and activate Sheet1. The data from the Samples Server that is running on the server side will fill in Sheet1.

  7. On the server side, you may see the following messages: