View Single Post
  #12  
Old 04-01-2010, 09:26 AM
popx's Avatar
popx popx is offline
Newcomer
 
Join Date: Mar 2010
Location: Oxford
Posts: 16
Default

Hi Mike,

I'm pleased to say that your answer works when transposed into Java. To test it, I made another pair of spreadsheets from scratch, one simulating the client one with the faulty macro, and one that calls it. I ran the latter from Java.

I hope it's OK to explain how here, because although Java isn't VB, we've now got so much useful stuff - some of which is independent of whether we're discussing VB - about the general problem that we might as well keep it here so it's easy to find.

To run Excel from Java, and to call Excel macros, I have been using a commercial library named EZ JCom by Desiderata Software. This makes COM objects available as Java classes and instances, and enables one to invoke their methods. There are also free libraries that do this, amongst them Dan Adler's Jacob, and Stuart Halloway and Justin Gehtland's Jawin. Yuri Gomes explains those in his Java COM Automation with Jacob and JBuilder, but I've not tried them, because the boss of this project wants EZ JCom.

I first made a spreadsheet named MyCalled.xls, which simulates a client's spreadsheet with macros, one of which is faulty. It has a blank workbook, and the following VBA module:

Code:
Public Sub Macro1()
  MsgBox "Macro1 called."
End Sub

Public Sub Macro2()
  MsgBox "Macro2 called."
  MsgBox 1 / 0
  ' Intentional divide-by-zero error.
End Sub

Public Sub Macro3()
  MsgBox "Macro3 called."
End Sub
I then changed the name of MyCalled's VBA project, by going to the VBA editor, selecting the VBA project's Properties, and overtyping the name with "MyCalled".

Next, I made another spreadsheet named MyCaller.xls, also with a blank workbook. To give it a reference to MyCalled.xls, I went to Tools in MyCaller.xls's VBA editor, selected References, then clicked the Browse button and, in the file-chooser, found the directory with MyCalled.xls in. I set the filetype in the file-chooser to "Microsoft Office Excel Files (*.xls, *.xla)", and selected MyCalled.xls. After exiting the file-chooser, a reference to MyCalled had got added, as evident from the presence of "MyCalled" with a ticked check-box next to it in the list of references.

I then put the following code into MyCaller.xls's ThisWorkbook module:

Code:
Public Sub CallMacro1()
  MsgBox "In caller, CallMacro1."
  [MyCalled].Macro1
End Sub

Public Sub CallMacro2()
  MsgBox "In caller, CallMacro2."
  [MyCalled].Macro2
End Sub

Public Sub CallMacro3()
  MsgBox "In caller, CallMacro3."
  [MyCalled.Macro3
End Sub
I tested this code by putting the cursor in each subroutine and pressing F5 to run it.

Then, I wrote the following Java code to call the above macros using EZ JCom. To repeat what I said above, EZ JCom makes COM objects available as Java classes and instances, and enables one to invoke their methods. In the code below, the 'wb' variable is the ThisWorkbook that contains the above VBA. My method callMacro invokes a macro in it by calling
Code:
wb.JComCallMethod( macro, macro_args );
where 'JComCallMethod' is an EZ JCom method for calling a method whose name is passed as a string. So here's my code:

Code:
package dobbs;

import java.io.*;  

// Import EZ JCom classes.
import excel.*; 
import ezjcom.*;

public class test
{
  public static void main(String args[])  
  {
    try {
      // Instantiate an Excel Application.
      Global global = new Global();
      
      // Get the Application instance into 'app', and
      // make it visible. This brings up Excel on the screen.
      _Application app = (_Application) global.get_Global().getApplication().get_Application();
      app.setVisible( true );

      // Open the MyCaller spreadsheet, and get its 
      // ThisWorkbook into 'wb'.
      String filename = "c:\\dobbs\\MyCaller.xls";
      _Workbook wb = app.getWorkbooks().Open( filename ).get_Workbook();

      callMacro( "CallMacro1", wb );

      callMacro( "CallMacro2", wb );

      callMacro( "CallMacro3", wb );
    } 
    catch ( Exception ex ) {
      System.out.println( "Exception:" );
      System.out.println( ex );
    } 
    finally {
      // Shut down COM connections and release all resources.
      ezjcom.JComObject.JComShutdown();
    }
  }

  private static void callMacro( String macro, _Workbook wb )
  {
    System.out.println( "Calling macro '" + macro + "' in MyCalled.xls." );
    try {
      JComVariant[] macro_args = {};
      wb.JComCallMethod( macro, macro_args );
    }
    catch ( Exception ex ) {
      System.out.println( "Exception during callMacro:" );
      System.out.println( ex );
    } 
    System.out.println( "Called macro '" + macro + "'." );
    System.out.println();
  }
}
I compiled and ran the code with the following DOS commands. The '-cp' command-line argument is a "classpath" pointing at the EZ JCom libraries; these can be downloaded free as evaluation copies from EZ JCom:
Code:
C:\dobbs>javac -cp c:\;c:\EZJcom\Jexcel9.jar;c:\ezjcom\ezjcom18.jar test.java

C:\dobbs>java -cp c:\;c:\EZJcom\Jexcel9.jar;c:\ezjcom\ezjcom18.jar dobbs.test
This gave the following output on the DOS console:
Code:
Calling macro 'CallMacro1' in MyCalled.xls.
Called macro 'CallMacro1'.

Calling macro 'CallMacro2' in MyCalled.xls.
Exception during callMacro:
ezjcom.JComException: Exception occurred. (0x80020009)
Called macro 'CallMacro2'.

Calling macro 'CallMacro3' in MyCalled.xls.
Called macro 'CallMacro3'.
I also saw six message boxes: three from MyCaller.xls and three from MyCalled.xls. But I didn't get the VBA "Run-time error '11': Division by zero" pop-up that I get when I run the faulty macro on its own.

So it looks as though I can now recover from macro errors without hanging the program that calls them. This would work, I should think, in any COM software that has an equivalent of JComCallMethod. So thanks hugely, Mike! One remaining question: what is error 0x80020009, and how could my program discover that (in this case) the real error was divide-by-zero?
Attached Files
File Type: zip MyCalled.zip (5.9 KB, 6 views)
File Type: zip MyCaller.zip (5.9 KB, 8 views)
File Type: zip test.zip (881 Bytes, 5 views)

Last edited by popx; 04-01-2010 at 11:58 AM. Reason: Correcting name change in Java code.
Reply With Quote