How to delete VB code from an Excel sheet using C#?

Question

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.

        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? :)

Answer 1

have you tried deleting the first one n times:

    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

EDIT:

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

Answer 2

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.

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