How to Read an Excel Personal Macro from java with Jacob

Question

I got to execute a macro of a excel file from jacob, but now i want to execute a personal.xlsb!mymacro and my java program throw error. I don't know how to write the name of the personal macro, i put:

String file="D:/Aprogramas/EclipseJEE/temp/Libro2.xlsm"

public boolean openFile`(String file) {
  try {
   final String macroName = "!PERSONAL.XLSB!Auto_abrir";
   executeMacro(new File(file), macroName);
   return true;
  } catch (Exception e) {
   e.printStackTrace();
  }
  return true;
 }


public boolean executeMacro(File file, String macroName) {
  ComThread.InitSTA();

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

  try {

   final Dispatch workbooks = excel.getProperty("Workbooks")
     .toDispatch();
   final Dispatch workBook = Dispatch.call(workbooks, "Open",
     file.getAbsolutePath()).toDispatch();

   final Variant result = Dispatch.call(excel, "Run",
     new Variant(file.getName() + macroName));

   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();
   return true;
  }

 }

The ERROR in consoles is:

com.jacob.com.ComFailException: Invoke of: Run
Source: Microsoft Excel
Description: No se puede ejecutar la macro "Libro2.xlsm!PERSONAL.XLSB!Auto_abrir". Puede que la macro no esté disponible en este libro o que se hayan deshabilitado todas las macros.

at com.jacob.com.Dispatch.invokev(Native Method)
at com.jacob.com.Dispatch.invokev(Dispatch.java:625)
at com.jacob.com.Dispatch.callN(Dispatch.java:453)
at com.jacob.com.Dispatch.call(Dispatch.java:541)
at com.test.main.POIExecutor.executeMacro(POIExecutor.java:43)
at com.test.main.POIExecutor.openFile(POIExecutor.java:23)
at com.test.main.Main.main(Main.java:11)

Answer 1

No need to append the file name with the macroname in the Dispatch.call method.

Remove "new Variant(file.getName() +" part from your Dispatch.call method

final Variant result = Dispatch.call(excel, "Run",
 **new Variant(file.getName() +** macroName));

The correct way is

final Variant result = Dispatch.call(excel, "Run", macroName));

It will execute without errors.