Programatically compiling VBA modules with c#

1

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# ?

c#
excel
automation
vba
asked on Stack Overflow Mar 14, 2018 by Olorin • edited Jul 9, 2018 by Community

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0