java - 如何使用 Jacob 从 Java 读取 Excel 个人宏

Question

我必须从 jacob 执行一个 excel 文件的宏，但现在我想执行一个 personal.xlsb!mymacro，我的 java 程序抛出错误。我不知道怎么写个人宏的名字，我把:

字符串文件="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;
  }

 }

控制台中的错误是:

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

Dispatch.call 方法中无需在文件名后附加宏名。

从您的 Dispatch.call 方法中删除 "new Variant(file.getName() +" 部分

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

正确的做法是

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

它将无错误地执行。