How to delete Excel.Name object?

Question

I have a bunch of Excel.Name objects under Workbook.Names collection which contain invalid references. In other words, when I evaluate the Excel.Name.RefersTo property, I get a value back beginning with "=#REF...". I tried to run the following code but it seems to have no effect in removing the names:

var ranges = myWorkBook.Names;
for (int i = 1; i <= ranges.Count; i++)
{
    var currentName = ranges.Item(i, Type.Missing, Type.Missing);
    var refersTo = currentName.RefersTo.ToString();
    if (refersTo.Contains("REF!"))
    {
        currentName.Delete();
    }
}

Can anyone suggest what I'm doing wrong here? Or maybe I'm missing a step?

Answer 1

Try Workbook.Names[i].Delete(). You cannot used currentName.Delete. (From memory, I will try to find my code to do this.)

Answer 2

AMissico led me in the right direction. currentName.Delete() definitely does not work. However there is no support for Workbook.Names[i].Delete under VSTO 2005 SE, so I dug around some more and discovered that Workbook.Names.Item(i, Type.Missing, Type.Missing) did the trick.

This is an example of the code that will work:

var ranges = myWorkBook.Names;

int i = 1;
while (i <= ranges.Count)
{
    var currentName = ranges.Item(i, Type.Missing, Type.Missing);
    var refersTo = currentName.RefersTo.ToString();
    if (refersTo.Contains("REF!"))
    {
        ranges.Item(i, Type.Missing, Type.Missing).Delete();
    }
    else
    {
        i++;
    }
}

Answer 3

To delete using the name:

workbook.names.Item(rangeName, Type.Missing, Type.Missing).Delete();

(in C# that is).