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

8
  • What is the value of test.VBE.CommandBars.FindControl(Id: 578) ? Commented Mar 14, 2018 at 19:56
  • I changed the code a bit : the new problematic line Microsoft.Office.Core.CommandBars listCommandBars = test.VBE.CommandBars; already triggers the {"Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY))"} exception. Commented Mar 14, 2018 at 20:23
  • Whereas an oXL.VBE.CommandBars.FindControl(MsoControlType.msoControlButton, Id: 587).Execute(); triggers an Exception thrown at 0x77E6ED6D (ntdll.dll) in XlamAutomation.exe: 0xC0000005: Access violation writing location 0x00000000. Commented Mar 14, 2018 at 20:34
  • What you are trying to do can generally only work from code that runs inside the Office app. Like VBA does. But not from an external C# program, the exception is trying to tell you that it does not know how the marshal the call from one process to another. Something that is hard to do, it needs a type library to pull it off and there isn't one available. You can write an Office add-in in C#, then it runs inside. Commented Mar 14, 2018 at 20:47
  • @HansPassant Thank you. To be precise, I am generating an xlam file with c#, adding modules to it from .bas files in a folder, and after all of that, I simply want to compile the vba code and save it. I understand your point, but why I can I run macros in the xlam from the c# wherease I can't compile the code itself from the c# ? What is different between running macros from outside and compiling from outside ? Commented Mar 14, 2018 at 21:13

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.