如何使用C＃从Excel工作表中删除VB代码？

Does anyone know how to delete all VB code form an Excel workbook using C#? This code doesn’t work. It removes first (last one) VBComponent, but rises ArgumentException on second one.

有谁知道如何使用C#从Excel工作簿中删除所有VB代码?此代码不起作用。它删除了第一个(最后一个)VBComponent,但在第二个上升了ArgumentException。

        VBProject project = workbook.VBProject;
        int componentsCount = project.VBComponents.Count;

        for (int i = componentsCount; i >= 1; i--)
        {
            VBComponent component = project.VBComponents.Item(i);
            project.VBComponents.Remove(component);
        }

Any suggestions? :)

有什么建议? :)

2 个解决方案

#1

I solved it with Sam's help. I suspect that each Excel workbook has some non-deletable VBComponents, hence instead of deleting them we can clear their content. It works now. Thank you Sam.

我在Sam的帮助下解决了这个问题。我怀疑每个Excel工作簿都有一些不可删除的VBComponents,因此我们可以清除它们的内容,而不是删除它们。它现在有效。谢谢山姆。

            VBProject project = workbook.VBProject;

        for (int i = project.VBComponents.Count; i >= 1; i--)
        {
            VBComponent component = project.VBComponents.Item(i);
            try
            {
                project.VBComponents.Remove(component);
            }
            catch(ArgumentException)
            {
                continue;
            }
        }

        for (int i = project.VBComponents.Count; i >= 1; i--)
        {
            VBComponent component = project.VBComponents.Item(i);
                component.CodeModule.DeleteLines(1, component.CodeModule.CountOfLines);
        }

Do not forget to save your workbook afterwards :)

不要忘记以后保存你的工作簿:)

#2

have you tried deleting the first one n times:

你试过删除第一次n次:

    VBProject project = workbook.VBProject;
    int componentsCount = project.VBComponents.Count;

    for (int i = 1; i <= componentsCount; i++)
    {
        VBComponent component = project.VBComponents.Item(1);
        project.VBComponents.Remove(component);
    }

you might need to tweak this, but i think the VBA collections are 1 based (might need to make the project.VBComponents.Item(0) instead

你可能需要调整一下,但我认为VBA集合是基于1的(可能需要制作project.VBComponents.Item(0)而不是

EDIT:

I found this post which explains how to do it in VBA, but presumably its not too difficult to translate...

我发现这篇文章解释了如何在VBA中做到这一点,但据推测它并不太难翻译......

#1