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

Community
  • 1
  • 1
Olórin
  • 3,367
  • 2
  • 22
  • 42
  • What is the value of test.VBE.CommandBars.FindControl(Id: 578) ? – PhillipH Mar 14 '18 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. – Olórin Mar 14 '18 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.` – Olórin Mar 14 '18 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. – Hans Passant Mar 14 '18 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 ? – Olórin Mar 14 '18 at 21:13
  • Because when you run them from outside you are asking Excel to load and compile them on your behalf. You are trying to invert that process. – PhillipH Mar 14 '18 at 22:38
  • Ok, I am now executing the code from an excel addin, and the same code throws `{"Error HRESULT E_FAIL has been returned from a call to a COM component."} commandbar find control` at me. Maybe I should somehow enable the command bar control before doing an execute, but how ? – Olórin Mar 16 '18 at 10:13
  • I have never heard of compiling VBA code, but see the link below for an idea of how to run an Excel Macro from C#. https://stackoverflow.com/questions/31200130/run-vba-macro-in-excel-sheet-using-external-c-sharp-console-application?rq=1 – ASH May 23 '18 at 17:30

0 Answers0