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();
}
}
}
}
`
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With