William Caman William Caman - 2 months ago 7
C# Question

C# Excel VBA make module names not depended on the language

excelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);


This code in English version of Office creates module named: "Module1". But if office language is different "Module1" will be in another language. I need to know how this module is called in my code.

var standardModule = excelFile.VBProject.VBComponents.Item("ThisWorkbook");


The same problem is here in English version of Office "ThisWorkbook" exits, but in another language it will be called differently.

It's possible to make this code language independent?

Answer

The first one is easy - VBComponents.Add returns a VBComponent. You can just inspect the .Name property:

var module = excelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
Debug.WriteLine(module.Name);

The second one is a bit trickier. You'll need to loop through all of the VBComponents and test for the 2 things that are unique to the Workbook object. It will have a .Type of vbext_ct_Document and 134 properties in its .Properties collection by default:

VBComponent thisWorkbook;
foreach (var module in excelFile.VBProject.VBComponents)
{
    var test = module as VBComponent;
    if (test.Type == vbext_ComponentType.vbext_ct_Document &&
        test.Properties.Count == 134)
    {
        thisWorkbook = test;
        Debug.WriteLine(thisWorkbook.Name);
        break;
    }
}

EDIT: The Linq solution looks like this, but it's possible that you could leave dangling Interop references this way. If you want to try it, it can't hurt - but it would be the first place I'd look if Excel doesn't shut down properly:

var thisWorkbook =
    (excelFile.VBProject.VBComponents).Cast<VBComponent>()
        .First(x => x.Type == vbext_ComponentType.vbext_ct_Document &&
                    x.Properties.Count == 134);
Comments