C＃Excel VBA使模块名称不依赖于语言

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.

此英文版Office中的代码创建名为“Module1”的模块。但如果办公室语言不同，“Module1”将使用另一种语言。我需要知道在我的代码中如何调用此模块。

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.

同样的问题出现在英文版的Office“ThisWorkbook”中，但在另一种语言中，它将被不同地调用。

It's possible to make this code language independent?

可以使这种代码语言独立吗？

3 个解决方案

#1

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

第一个很容易--VBComponents.Add返回一个VBComponent。您只需检查.Name属性：

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并测试Workbook对象独有的两件事。默认情况下，它的.Properties集合中将包含.type of vbext_ct_Document和134个属性：

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:

编辑：Linq解决方案看起来像这样，但你可能会以这种方式留下悬挂的Interop引用。如果你想尝试它，它不会受到伤害 - 但如果Excel没有正确关闭它将是我看的第一个地方：

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

EDIT2: As pointed out by @Mat'sMug in the comments, the property count is specific to the version - the value above is probably specific to Excel 2013. For a new workbook, the ThisWorkbook module will be the one with the highest property count. This should work on any version:

EDIT2：正如@ Mat'sMug在评论中所指出的，属性计数是特定于版本的 - 上面的值可能特定于Excel 2013.对于新工作簿，ThisWorkbook模块将是具有最高属性数的模块。这适用于任何版本：

VBComponent thisWorkbook = null;
foreach (var component in excelFile.VBProject.VBComponents.Cast<VBComponent>())
{
    if (thisWorkbook == null || component.Properties.Count > thisWorkbook.Properties.Count)
    {
        thisWorkbook = component;
    }
}
Debug.WriteLine(thisWorkbook.Name);

Linq:

LINQ：

var thisWorkbook =
    excelFile.VBProject.VBComponents.Cast<VBComponent>()
        .Aggregate((p, x) => (p.Properties.Count > x.Properties.Count ? p : x));

#2

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

VBComponents.Add is a function, returning the object that was just added - but you're discarding that reference.

VBComponents.Add是一个函数，返回刚添加的对象 - 但是你丢弃了那个引用。

All you need to do is to hold on to that reference:

您需要做的就是坚持这个参考：

var component = excelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);

And then you can rename it at will (must be unique in the parent project though):

然后你可以随意重命名它（虽然在父项目中必须是唯一的）：

component.Name = "RenameMe";

...Or if you just need to know its name, it's right there for you to read:

...或者如果你只需要知道它的名字，它就在那里你可以阅读：

Debug.WriteLine(component.Name);

#3

-1

Since you just added that module in your code, it should be on the last index position, thus it should be accesible by doing:

由于您刚刚在代码中添加了该模块，因此它应该位于最后一个索引位置，因此可以通过执行以下操作来访问它：

VBComponent ModuleIJustAdded = excelFile.VBProject.VBComponents.Item(excelFile.VBProject.VBComponents.Count - 1);

Since you are unsure about the module's name, I suggest you try to play around the index.

由于您不确定该模块的名称，我建议您尝试使用该索引。

#1