CODEWITHSUNDEEP
c#vbaexcel
dinotom
dinotom

Reputation: 5162

Accessing a specific VBA code module using C#

I am creating workbooks from a template. The template already has the required code modules in it. I am trying to get a reference to one of the code modules, the one that the code needs to be imported to (the code is always different so having a .bas file wont work here as there would be hundreds of them).

I can easily access a new code module using

var codeModule = excelWorkbook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);

But I need to access the existing "Methods" code module in the instantiated workbook variable

 //initialize the Excel application and make it invisible to the user.
        var excelApp = new Excel.Application
        {
            UserControl = false,
            Visible = false
        };

        //Create the Excel workbook and worksheet - and give the worksheet a name.
        var excelWorkbook = excelApp.Workbooks.Open(TemplateFilePath);
        excelWorkbook.Author = Acknowledgements.Author;
        var bookPath = excelWorkbook.Path;

        //add the macro code to the excel workbook here in the Methods module.
       //this adds a new module, how do I access an existing one?
        var codeModule = excelWorkbook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);

I could just loop through the modules and get it by testing name equality but there must be a way to retrieve it in one line of code.

Upvotes: 4

Views: 2172

Answers (1)

Michael Liu
Michael Liu

Reputation: 55499

Use the Item method of the VBComponents collection to retrieve an existing VBComponent by name or numeric index:

using VBIDE = Microsoft.Vbe.Interop;

VBIDE.VBComponent component = excelWorkbook.VBProject.VBComponents.Item("Methods");
VBIDE.CodeModule module = component.CodeModule;

Upvotes: 4

Related Questions