c# - How can I run a macro from a VBE add-in, without Application.Run? -
i'm writing com add-in vbe, , 1 of core features involves executing existing vba code upon clicking commandbar button.
the code unit testing code written user, in standard (.bas) module looks this:
option explicit option private module '@testmodule private assert new rubberduck.assertclass '@testmethod public sub testmethod1() 'todo: rename test on error goto testfail 'arrange: 'act: 'assert: assert.inconclusive testexit: exit sub testfail: assert.fail "test raised error: #" & err.number & " - " & err.description end sub
so have code gets current instance of host application object:
protected hostapplicationbase(string applicationname) { application = (tapplication)marshal.getactiveobject(applicationname + ".application"); } here's excelapp class:
public class excelapp : hostapplicationbase<microsoft.office.interop.excel.application> { public excelapp() : base("excel") { } public override void run(qualifiedmembername qualifiedmembername) { var call = generatemethodcall(qualifiedmembername); application.run(call); } protected virtual string generatemethodcall(qualifiedmembername qualifiedmembername) { return qualifiedmembername.tostring(); } } works charm. have similar code wordapp, powerpointapp , accessapp, too.
the problem outlook's application object doesn't expose run method, i'm, well, stuck.
how can execute vba code com add-in vbe, without application.run?
this answer links blog post on msdn looks promising, tried this:
public class outlookapp : hostapplicationbase<microsoft.office.interop.outlook.application> { public outlookapp() : base("outlook") { } public override void run(qualifiedmembername qualifiedmembername) { var app = application.gettype(); app.invokemember(qualifiedmembername.membername, bindingflags.invokemethod, null, application, null); } } but best i'm getting comexception says "unknown name", , outlook.exe process exiting code -1073741819 (0xc0000005) 'access violation' - , blows nicely excel, too.
update
this vba code works, if put testmethod1 inside thisoutlooksession:
outlook.application.testmethod1 note testmethod1 isn't listed member of outlook.application in vba intellisense.. somehow happens work.
the question is, how make work reflection?
update 3:
i found post on msdn forums: call outlook vba sub vsto.
obviously uses vsto , tried converting vbe addin, ran issues @ work x64 windows register class issue:
comexception (0x80040154): retrieving com class factory component clsid {55f88893-7708-11d1-aceb-006008961da5} failed due following error: 80040154 class not registered
anyway guys answer reckons got working:
start of msdn forum post
i found way! triggered both vsto , vba? clipboard!!
so used clipboard pass messages 1 environment other. here few codes explain trick:
vsto:
'p_procedure procedure name call in vba within outlook 'mobj_ou_userproperty create custom property pass argument vba procedure private sub p_call_vba(p_procedure string) dim mobj_of_commandbars microsoft.office.core.commandbars, mobj_ou_explorer outlook.explorer, mobj_ou_mailitem outlook.mailitem, mobj_ou_userproperty outlook.userproperty mobj_ou_explorer = globals.menu_addin.application.activeexplorer 'i want run when 1 item selected if mobj_ou_explorer.selection.count = 1 mobj_ou_mailitem = mobj_ou_explorer.selection(1) mobj_ou_userproperty = mobj_ou_mailitem.userproperties.add("com addin-azimuth", outlook.oluserpropertytype.oltext) mobj_ou_userproperty.value = p_procedure mobj_of_commandbars = mobj_ou_explorer.commandbars 'call clipboard event copy mobj_of_commandbars.executemso("copy") end if end sub vba:
create class explorer events , trap event:
public withevents mpubobj_explorer explorer 'trap clipboard event copy private sub mpubobj_explorer_beforeitemcopy(cancel boolean) dim mobj_mi mailitem, mobj_userproperty userproperty 'make sure 1 item selected , of type mail if mpubobj_explorer.selection.count = 1 , mpubobj_explorer.selection(1).class = olmail set mobj_mi = mpubobj_explorer.selection(1) 'check see if custom property present in mail selected each mobj_userproperty in mobj_mi.userproperties if mobj_userproperty.name = "com addin-azimuth" select case mobj_userproperty.value case "example_add_project" '... case "example_modify_planning" '... end select 'remove custom property, keep things clean mobj_userproperty.delete 'cancel copy event. makes call transparent user cancel = true exit end if next set mobj_userproperty = nothing set mobj_mi = nothing end if end sub end of msdn forum post
so author of code adds userproperty mail item , passes function name way. again require boiler plate code in outlook , @ least 1 mail item.
update 3a:
the 80040154 class not registered getting because despite targeting x86 platform when translated code vsto vb.net vbe c# instantiating items, eg:
microsoft.office.core.commandbars mobj_of_commandbars = new microsoft.office.core.commandbars(); after wasting several more hours on it, came code, ran!!!
the vbe c# code (from answer make vbe addin answer here):
namespace vbeaddin { [comvisible(true), guid("3599862b-ff92-42df-bb55-dbd37cc13565"), progid("vbeaddin.connect")] public class connect : idtextensibility2 { private vbe _vbe; private addin _addin; #region "idtextensibility2 members" public void onconnection(object application, ext_connectmode connectmode, object addininst, ref array custom) { try { _vbe = (vbe)application; _addin = (addin)addininst; switch (connectmode) { case extensibility.ext_connectmode.ext_cm_startup: break; case extensibility.ext_connectmode.ext_cm_afterstartup: initializeaddin(); break; } } catch (exception ex) { messagebox.show(ex.tostring()); } } private void onreferenceitemadded(reference reference) { //todo: map types found in assembly using reference. } private void onreferenceitemremoved(reference reference) { //todo: remove types found in assembly using reference. } public void ondisconnection(ext_disconnectmode disconnectmode, ref array custom) { } public void onaddinsupdate(ref array custom) { } public void onstartupcomplete(ref array custom) { initializeaddin(); } private void initializeaddin() { messagebox.show(_addin.progid + " loaded in vba editor version " + _vbe.version); form1 frm = new form1(); frm.show(); //<-- here instantiating form when addin loads vbe ide! } public void onbeginshutdown(ref array custom) { } #endregion } } the form1 code instantiate , load vbe ide initializeaddin() method:
namespace vbeaddin { public partial class form1 : form { public form1() { initializecomponent(); } private void button1_click(object sender, eventargs e) { call_vba("test"); } private void call_vba(string p_procedure) { var olapp = new microsoft.office.interop.outlook.application(); microsoft.office.core.commandbars mobj_of_commandbars; microsoft.office.core.commandbars mobj_of_commandbars = new microsoft.office.core.commandbars(); microsoft.office.interop.outlook.explorer mobj_ou_explorer; microsoft.office.interop.outlook.mailitem mobj_ou_mailitem; microsoft.office.interop.outlook.userproperty mobj_ou_userproperty; //mobj_ou_explorer = globals.menu_addin.application.activeexplorer mobj_ou_explorer = olapp.activeexplorer(); //i want run when 1 item selected if (mobj_ou_explorer.selection.count == 1) { mobj_ou_mailitem = mobj_ou_explorer.selection[1]; mobj_ou_userproperty = mobj_ou_mailitem.userproperties.add("jt", microsoft.office.interop.outlook.oluserpropertytype.oltext); mobj_ou_userproperty.value = p_procedure; mobj_of_commandbars = mobj_ou_explorer.commandbars; //call clipboard event copy mobj_of_commandbars.executemso("copy"); } } } } the thisoutlooksession code:
public withevents mpubobj_explorer explorer 'trap clipboard event copy private sub mpubobj_explorer_beforeitemcopy(cancel boolean) dim mobj_mi mailitem, mobj_userproperty userproperty msgbox ("the mpubobj_explorer_beforeitemcopy event worked!") 'make sure 1 item selected , of type mail if mpubobj_explorer.selection.count = 1 , mpubobj_explorer.selection(1).class = olmail set mobj_mi = mpubobj_explorer.selection(1) 'check see if custom property present in mail selected each mobj_userproperty in mobj_mi.userproperties if mobj_userproperty.name = "jt" 'will magic happen?! outlook.application.test 'remove custom property, keep things clean mobj_userproperty.delete 'cancel copy event. makes call transparent user cancel = true exit end if next set mobj_userproperty = nothing set mobj_mi = nothing end if end sub the outlook vba method:
public sub test() msgbox ("will called?") end sub very sadly, regret inform efforts unsuccessful. maybe work vsto (i haven't tried) after trying dog fetching bone, willing give up!
never less consolation can find crazy idea in revision history of answer (it shows way of mocking office object model) run office vba unit tests private parameters.
i speak offline contributing rubberduck github project, wrote code same thing prodiance's workbook relationship diagram before microsoft bought them out , included product in office audit , version control server.
you may wish examine code before dismissing entirely, couldn't mpubobj_explorer_beforeitemcopy event work, if can working in outlook might fare better. (i'm using outlook 2013 @ home, 2010 might different).
ps think after hopping on 1 leg in anti-clockwise direction, clicking fingers while rubbing head clockwise like workaround method 2 in kb article have nailed it... nup lost more hair!
update 2:
inside outlook.application.testmethod1 can't use vb classics callbyname method dont need reflection? you'd need set string property "sub/functionnametocall" before calling method containing callbyname specify sub/function call.
unfortunately users required insert boiler plate code in 1 of module's.
update 1:
this going sound really dodgy, since outlooks' object model has clamped down run method resort to... sendkeys (yeah know, work).
unfortunately oapp.gettype().invokemember("run"...) method described below works all office apps except outlook - based on properties section in kb article: https://support.microsoft.com/en-us/kb/306683, sorry didn't know until , found frustrating trying , the msdn article misleading, microsoft has locked it:
** note sendkeys supported , other known way using thisoutlooksession not: https://groups.google.com/forum/?hl=en#!topic/microsoft.public.outlook.program_vba/cq8gf9ssn3g - though sue isn't microsoft pss she would've asked , found out unsupported.
old... below method works office apps except outlook
the problem outlook's application object doesn't expose run method, i'm, well, stuck. answer links blog post on msdn looks promising, tried ... outlook.exe process exits code -1073741819 (0xc0000005) 'access violation'
the question is, how make work reflection?
1) here code use works excel (should work outlook same), using .net reference: microsoft.office.interop.excel v14 (not activex com reference):
using system; using microsoft.office.interop.excel; namespace consoleapplication5 { class program { static void main(string[] args) { runvbatest(); } public static void runvbatest() { application oexcel = new application(); oexcel.visible = true; workbooks obooks = oexcel.workbooks; _workbook obook = null; obook = obooks.open("c:\\temp\\book1.xlsm"); // run macro. runmacro(oexcel, new object[] { "testmsg" }); // quit excel , clean (its better use vstocontrib jake ginnivan). obook.saved = true; obook.close(false); system.runtime.interopservices.marshal.releasecomobject(obook); system.runtime.interopservices.marshal.releasecomobject(obooks); system.runtime.interopservices.marshal.releasecomobject(oexcel); } private static void runmacro(object oapp, object[] orunargs) { oapp.gettype().invokemember("run", system.reflection.bindingflags.default | system.reflection.bindingflags.invokemethod, null, oapp, orunargs); //your call looks little bit wack in comparison, using instance of app? //application.gettype().invokemember(qualifiedmembername.membername, bindingflags.invokemethod, null, application, null); } } } } 2) make sure put macro code in module (a global bas file)..
public sub testmsg() msgbox ("hello stackoverflow") end sub 3) make sure enable macro security , trust access vba project object model:
Comments
Post a Comment