Jacob: Call to VBA failed

Question

I have a Excel VBA written and trying to call it from java (using Jacob) as below:

public void PnlCubeExcelMacroCallAndRefresh() {
  ComThread.InitSTA();
  File file=new File("C:\\Users\\Pnl_Cube_Template.xlsm");
 // String macroName="Retrieve()";
   String macroName="Retrieve()";

    final ActiveXComponent excel = new ActiveXComponent("Excel.Application");

    try {
        // This will open the excel if the property is set to true
        excel.setProperty("Visible", new Variant(true));
        final Dispatch workbooks = excel.getProperty("Workbooks").getDispatch();
        //String    eventSink = null ;
        int id = Dispatch.get(workbooks, "Count").getInt();
        System.out.println("le nbre" + id);
        Dispatch.call(workbooks, "Add");
        Dispatch workBook = Dispatch.call(workbooks, "Open", file.getAbsolutePath()).toDispatch();

        Variant V1 = new Variant( file.getName() + macroName);
        // Calls the macro
        final Variant result = Dispatch.call(excel, "Run", V1);

        // Saves and closes
        //Dispatch.call(workBook, "Save");

        com.jacob.com.Variant f = new com.jacob.com.Variant(true);
        //  Dispatch.call(workBook, "Close", f);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {

        excel.invoke("Quit", new Variant[0]);
        ComThread.Release();
    }
}

But when I run this below error is encountered:

com.jacob.com.ComFailException: Invoke of: Run Source: Microsoft Excel Description: Cannot run the macro 'Pnl_Cube_Template.xlsmRetrieve'. The macro may not be available in this workbook or all macros may be disabled.

I have tried changing the trust center settings as well, still same issue.

enter image description here

Well, do you have a macro in the workbook called `Pnl_Cube_Template.xlsmRetrieve`? — user253751, Mar 24 '15 at 08:16
I see a macro called `Retrieve`, not a macro called `Pnl_Cube_Template.xlsmRetrieve`. — user253751, Mar 24 '15 at 08:33
@Vinod are you quite sure that name is correct? Maybe try just "Retrieve". — Andy Turner, Mar 24 '15 at 08:35
@immibis: I have changed the Variant to just the macro name, now its running till 3rd line of the macro and then I get a run-time error: Cannot run the macro 'EssMenuRetrieve' — Vinod, Mar 24 '15 at 09:02
Am able to run the macro via excel, when I call the macro via above method its throwing the run time error. — Vinod, Mar 24 '15 at 09:25

score 0 · Answer 1 · edited Jun 30 '16 at 18:49

0

Modify your code

Variant V1 = new Variant( file.getName() + "!"+macroName);

and

macroName = "Retrieve"

without the parentheses

edited Jun 30 '16 at 18:49

Tony Hinkle

4,706
7
23
35

answered Jun 30 '16 at 18:29

Francisco Euán López

1

Jacob: Call to VBA failed

1 Answers1