Automation Error with conditional compilation
In Automation mode Excel does not load add-ins by default. Excel loads add-ins using the conditions add-ins were compiled with. In order for Add-In works during Automation mode, one should force Excel to load it prior to load any workbooks depending on that add-in. Below I provide the code example from my real project (with some editions) which implements this loading sequence in JScript. Comments explain steps.
function run_excel() { dbg_log("run_excel"); g_xla_addin = null; g_xla = null; try { g_add_ins = get_excel_app().AddIns; dbg_log("finding add_in.xlam"); //Searching for the installed add-in like Application.COMAddIns("WMExcelAddin1") g_xla_addin = find_addin(g_add_ins, "add_in.xlam"); } catch(v_err) { throw new error( "xla_loading" , CR_xla_loading , 'Unexpected error occurred while determining if add_in.xlam is installed.' , v_err ); } if (g_xla_addin == null) throw new error( "xla_loading" , CR_xla_not_installed , "MS Excel addin is not installed." ); try { dbg_log("opening add_in.xlam"); //In the example, the add-in has the name of its workbook try { g_xla = g_excel.Workbooks(g_xla_addin.Name); } catch(e) {} if (g_xla == null) { g_excel.AutomationSecurity = 1; // 1 == msoAutomationSecurityLow //Loading the add-in. The add-in also handles `OpenWorkbook` at this time. g_xla = g_excel.Workbooks.Open( g_xla_addin.FullName //FileName , 2 //UpdateLinks , true //ReadOnly , null //Format , null //Password , null //WriteResPassword , true //IgnoreReadOnlyRecommended: not display the read-only recommended message , 2 //Origin: xlWindows , null //Delimiter , null //Editable , null //Notify , null //Converter , false //AddToMru: don't add this workbook to the list of recently used files , true //Local: saves files against the language of Microsoft Excel. , 0 //CorruptLoad: xlNormalLoad ); hide_excel(); //To speed up and not interfere with user actions } } catch(v_err) { throw new error( "xla_loading" , CR_xla_loading , 'Unexpected error occurred while loading add_in.xlam:\n' , v_err ); } //Now the Add-In is loaded, so the VBA engine knows its API, and the workbook referencing to it are loaded fine. try { g_sig_cat_wbk = g_excel.Workbooks.Open( g_sig_cat_fn //FileName , 2 //UpdateLinks , true //ReadOnly , null //Format , null //Password , null //WriteResPassword , true //IgnoreReadOnlyRecommended: not display the read-only recommended message , 2 //Origin: xlWindows , null //Delimiter , null //Editable , null //Notify , null //Converter , false //AddToMru: don't add this workbook to the list of recently used files , false //Local: saves files against the language of Microsoft Excel. , 0 //CorruptLoad: xlNormalLoad );//Calling on the loaded workbook the target macro from the loaded add_in.xlam vba_ret(g_excel.Run(g_xla_addin.Name+"!my_addin.prepare_sig_import", g_sig_cat_wbk.Name)); } catch(v_err) { throw new error( "sig_cat_loading" , CR_sig_cat_loading , 'Error occured while loading catalog of signals:\n' , v_err ); } finally { g_sig_cat_wbk.Close(false); g_sig_cat_wbk = null; } dbg_log("run_excel done");}