How to disable VB6 MsgBox from java Code using Jacob

Question

I'm using JACOB API to call some Sub from VB macro. I would like to block the MsgBox generated by this macro.

This is my code to open macro XXXX.xls and run the sub traiteOT who contains some MsgBox.

    `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(); 
            //String    eventSink = null ;

            Dispatch.call(workbooks,"Add");
         Dispatch workBook = Dispatch.call(workbooks,"Open", file.getAbsolutePath()).toDispatch();
            ExcelEventHandler w = new ExcelEventHandler();

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

    public static void main(String[] args) {
        ExcelMacroTest emt = null;
        try {

            final File file = new File("D:XXXXXXXX.xls");
            final String macroName = "!TraiteOT";
            callExcelMacro(file, macroName);

        } finally {
            if (emt != null) {
                emt.quit();
            }
        }
    }
}

`

score 1 · Answer 1 · answered Mar 25 '13 at 17:02

1

you cannot block the msgbox unless you comment the code that executes the function OR, avoid calling that code in some other way.

If you really had to, you could read the VBA code into a variable, strip out the Msgbox function call, then execute it (using Visual Basic For Applications Extensibility)

better to just write a special function without the MsgBox in the workbook

answered Mar 25 '13 at 17:02

Our Man in Bananas

5,809
21
91
148

Thanks Philip for your answer. But, I would like to call a subroutine from VB Macro without change on the code of this macro. Then, can you explain more about the solution with use of Visual Basic For Applications Extensibility ? – khaled Rihane Mar 25 '13 at 21:37
well, it basically means you programmatically create the procedure the way you want to execute it, then execute it...read at [Ozgrid](http://www.ozgrid.com/forum/showthread.php?t=48694) and [programmatically-add-macro-to-excel](http://vbadud.blogspot.com/2008/10/programmatically-add-macro-to-excel.html) – Our Man in Bananas Mar 26 '13 at 09:26
This procedure is already implemented into macro and i wish to take benefit from it. Then, if it will be run it can display a msgBox which block the running of the procedure . – khaled Rihane Mar 26 '13 at 11:25
there is **no simple way** to block the Messagebox function.You would have to copy all of the code without the Msgbox function call – Our Man in Bananas Mar 26 '13 at 12:01
Can I access to the code of macro and comment the lines which contains this MsgBox from Java(Jacob). – khaled Rihane Mar 27 '13 at 10:09
Then, we use jacob to running VBscript which contain the Excel VBA Extensibility Code – khaled Rihane Mar 27 '13 at 13:23
yes, [CLICK HERE: Excel VBA Extensibility](http://www.cpearson.com/excel/vbe.aspx) – Our Man in Bananas Mar 27 '13 at 13:50

How to disable VB6 MsgBox from java Code using Jacob

1 Answers1