Running Personal macros with JACOB in Java

Question

This question has been asked before Here but it never received any answers.

If I save a macro to a specific (Non Personal Macro) workbook, then I am able to get the macro to run.

I am unable to get a Personal Macro to work after opening up an Excel file. It gives me the error.

com.jacob.com.ComFailException: Invoke of: Run Source: Microsoft Excel Description: Sorry, we couldn't find C:\Users\Me\Documents\!PERSONAL.XLSB. Is it possible it was moved, renamed or deleted?

Logic suggests that the program is looking for my Macro at the file path declared in the error, but I'm a little confused as to why it's checking that file path in the first place. Furthermore, if it is checking the wrong file path, how do I get it to direct towards the right one.

My Code:

 public static void main(String[] args) throws IOException{
 final File file = new File( "path");
            final String macroName = "!PERSONAL.XLSB!Macro";
            callExcelMacro(file, macroName);


  }
private static void callExcelMacro(File file, String macroName) {
        ComThread.InitSTA();
        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")
                .toDispatch();
        final Dispatch workBook = Dispatch.call(workbooks, "Open",
                file.getAbsolutePath()).toDispatch();

        // Calls the macro
       final Variant result = Dispatch.call(excel, "Run", macroName);

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

}

Is your `PERSONAL.XLSB` open when you try to run the macro? When you start Excel via automation it typically doesn't open any personal macro workbook or load any add-ins etc: you have to do this via code. — Tim Williams, Jan 08 '16 at 19:29
However you want, but since you're using Java here that would seem to be the best way... — Tim Williams, Jan 08 '16 at 21:30
hmmm.. I've only been working with Java for a couple weeks now, not sure how I would go about doing this. — Jcmoney1010, Jan 08 '16 at 21:44
I don't use Java at all, but I'm guessing you will need to add something like this: `final Dispatch workBookP = Dispatch.call(workbooks, "Open", "fullPathToYourPersonalXlsbFileHere").toDispatch();` — Tim Williams, Jan 08 '16 at 21:55

Running Personal macros with JACOB in Java

0 Answers0