Programatically compiling VBA modules with c#

2019-07-31 10:59发布

I have the following code

Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
Workbook oWB = oXL.Workbooks.Open(solutionDirectory);

where string solutionDirectory contains the path to an Excel xlam file. It is and Excel Macro-Enabled Add-In file that is used to add new functions to Excel : if you open it with Excel, you won't have a spreadsheet, but only VBA code. When modified in VBA, this code can be compiled via VBA.

I try to trigger programmatically this compilation with c# :

VBComponents = oWB.VBProject.VBComponents;
foreach (var module in VBComponents)
{
    var test = module as VBComponent;
    if (test.Type == vbext_ComponentType.vbext_ct_StdModule || test.Type == vbext_ComponentType.vbext_ct_ClassModule)
    {
        Microsoft.Office.Core.CommandBars listCommandBars = test.VBE.CommandBars; // first problematic line
        listCommandBars.FindControl(Id: 578).Execute(); // problematic line
    }
}

I found inspiration for the "problematic line" here :

https://www.experts-exchange.com/questions/26424766/Programmatically-check-VBA-compiles-as-part-of-release-procedure.html

and tried to adapt, without success : the problematic line triggers a :

{"Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY))"}

I am wrong on the way to do the compilation, or is the problem of a different nature ? What's the way to achieve programatic compilation of VBA modules with c# ?

0条回答
登录 后发表回答