dinotom dinotom - 1 year ago 189
C# Question

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.

Answer Source

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

VBIDE.VBComponent component = excelWorkbook.VBProject.VBComponents.Item("Methods");
VBIDE.CodeModule module = component.CodeModule;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download