C＃Excel VBA使模块名称不依赖于该语言 [英] C# Excel VBA make module names not depended on the language

问题描述

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

这个英文版本的Office代码创建了名为Module1的模块。但是如果办公语言不一样，Module1将用另一种语言进行。我需要知道这个模块在代码中的调用方式。

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");    

同样的问题在这里英文版的OfficeThisWorkbook退出，但在另一种语言将

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?

推荐答案

第一个很简单 - VBComponents.Add 返回一个 VBComponent 。您可以检查 .Name 属性：

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);

第二个是有点棘手。您将需要循环遍历所有VBComponents并测试Workbook对象唯一的2项。它将在 .Properties .Type vbext_ct_Document 和134个属性。 c $ c>收集默认情况下：

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;
    }
}

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

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);

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

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:

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：

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

这篇关于C＃Excel VBA使模块名称不依赖于该语言的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！