How to run a Excel VBA Macro from Java?

Question

i need to run a Excel VBA Macro from Java. I've already exported the module as a vbs file: module1.vbs

And now i just need to run it from the java code.

I tried to run it from Java like this:

Runtime.getRuntime().exec("C://Users//pk//OneDrive - pk//Documents//Module1.vbs");

This is the Error i get from it:

java.io.IOException: Cannot run program "C:\Users\pk\OneDrive - pk\Documents\Modul1e.vbs": CreateProcess error=193, %1 is not a valid Win32 application

Answer 1

The 2 options I can think of:

1. Either install a VB runtime that can run VBA files

2. Use Jacob (Java COM bridge) to connect to Excel (or any other MS Office App), then run the VBAProject in there

I don't have any concrete suggestions about the first one.

The second one would encase the following steps:

Get new Excel instance

ActiveXComponent excel = new ActiveXComponent(ApplicationIdentifiers.EXCEL.OleAddress);

Get hold of all the loaded workbooks (The class Workbook is a custom class of mine, simply wrapping the created Dispatch):

public ArrayList<Workbook> getWorkbooks() {
    final ArrayList<Workbook> ret = new ArrayList<>();
    final Dispatch workbooks = excel.getProperty("Workbooks").getDispatch();
    final int count = Dispatch.get(workbooks, "Count").getInt();
    for (int i = 1; i <= count; ++i) {
        final Dispatch oneworkbook = Dispatch.invoke(workbooks, "Item", Dispatch.Get, new Object[] { Integer.valueOf(i) }, new int[0]).getDispatch();
        final Workbook wb = new Workbook(this, oneworkbook);
        ret.add(wb);
    }
    return ret;
}

... or open a file manually, I just don't have the code for that here, probably something along the lines of

        final Dispatch newworkbook = Dispatch.invoke(workbooks, "Open", Dispatch.Get, new Object[] { filename }, moreparams).getDispatch();

Then, get the contained VBProject:

        final Dispatch vbp = Dispatch.get(oneworkbook, "VBProject").toDispatch();

In there, you could get the VBComponents:

            final Dispatch components = Dispatch.get(vbp, "VBComponents").toDispatch();

Get component count like this:

    return Dispatch.get(components, "Count").getInt();

And in there, iterate to count (pIndex), you can get the Modules/Forms/classes by so:

    final Dispatch item = Dispatch.call(components, "Item", Integer.valueOf(pIndex)).toDispatch();

The respective code module so:

    final Dispatch d = Dispatch.get(item, "CodeModule").toDispatch();

and from there you'd have to do a bit of your own research, getting the right method and then run it.

The identifiers for accessing/creating Office apps I have compiled here:

public enum ApplicationIdentifiers {
    ACCESS("Access.Application", "Access.CodeData", "Access.CurrentData", "Access.CodeProject", "Access.CurrentProject", "Access.DefaultWebOptions"), //
    EXCEL("Excel.Application", "Excel.AddIn", "Excel.Chart", "Excel.Sheet"), //
    FILE_EXPLORER("Shell.Explorer.1"), //
    GRAPH("MSGraph.Application", "MSGraph.Chart"), //
    OUTLOOK("Outlook.Application", "Outlook.OlkBusinessCardControl", "Outlook.OlkCategoryStrip", "Outlook.OlkCheckBox", "Outlook.OlkComboBox", "Outlook.OlkCommandButton", "Outlook.OlkContactPhoto", "Outlook.OlkDateControl", "Outlook.OlkFrameHeader", "Outlook.OlkInfoBar", "Outlook.OlkLabel", "Outlook.OlkListBox", "Outlook.OlkOptionButton", "Outlook.OlkPageControl", "Outlook.OlkSenderPhoto", "Outlook.OlkTextBox", "Outlook.OlkTimeControl", "Outlook.OlkTimeZone"), //
    POWERPOINT("PowerPoint.Application"), //
    WORD("Word.Application", "Word.Document", "Word.Template", "Word.Global"), //
    ;



    public final String     OleAddress;
    public final String[]   Components;
    
    private ApplicationIdentifiers(final String pOleAddress, final String... pComponents) {
        OleAddress = pOleAddress;
        Components = pComponents;
    }

    @Override public String toString() {
        return "Application: " + OleAddress + ", Components: " + Components;
    }
}

Answer 2

If you want to execute a VBA macro, you need to open the host application (in your case, that's Excel). Simply storing the macro code as VBS file will not work - VBS and VBA are similar, but not identically, and VBS does not know anything about Excel.

Easiest way to open Excel and execute a Macro is to write a VBS script as wrapper that is started from you Java code. That script starts the Excel Application, opens the specific Excel file and start the macro you want to run. Note that the Sub should be stored in a regular module, not in Workbook or Worksheet module.

A VBScript on windows is started with CScript (or WScript) - before you add it to your Java code, try it on the windows command line. Be sure that it is executed successfully.

I am not familiar with the Java Exec statement, but I assume it would look like this

Runtime.getRuntime().exec("CScript \"C://Users//pk//OneDrive - pk//Documents//startExcelWithMacro.vbs\"");

For an example code, have a look to Run Excel Macro from Outside Excel Using VBScript From Command Line