Search code examples
c#exceloffice-interop

Excel Interop - Add Worksheet and change name via return value


i'm trying to change the name of a Worksheet created via the Add method with the usage of it's return value. This is suggested by several anwsers on stackoverflow. But it doesn't work and gives an COMException with the error code 0x800401A8. The other two methods work fine. Can someone give me a hint why the first method isn't working?

The Code:

Application excel = new Application();
Workbook wb = excel.Workbooks.Open(fileInfo.FullName);

// Creates a new worksheet, chart, or macro sheet. The new worksheet becomes the active sheet.
var sheet = wb.Sheets.Add(Before: Type.Missing, After: wb.Sheets[wb.Sheets.Count], Count: 1, Type: XlSheetType.xlWorksheet);

try
{
    ((Worksheet)sheet).Name = "ASDF";
    
    
} catch(COMException e)
{
    Trace.WriteLine($"Fail 1 - 0x{e.ErrorCode:X}");
}
try
{
    ((Worksheet)wb.ActiveSheet).Name = "ASDF";
} catch(COMException e)
{
    Trace.WriteLine($"Fail 2 - 0x{e.ErrorCode:X}");
}

try
{
    ((Worksheet) wb.Sheets[wb.Sheets.Count]).Name = "ASDF";
} catch (COMException e)
{
    Trace.WriteLine($"Fail 3 - 0x{e.ErrorCode:X}");
}

The Trace Output:

Fail 1 - 0x800401A8

The exception in detail:

threw an exception of type 'System.Runtime.InteropServices.COMException'
    Data: {System.Collections.ListDictionaryInternal}
    ErrorCode: -2147221080
    HResult: -2147221080
    HelpLink: null
    InnerException: null
    Message: "0x800401A8"
    Source: "Testfall-Manager"
    StackTrace: "   at Microsoft.Office.Interop.Excel._Worksheet.set_Name(String RHS)"
    TargetSite: {Void set_Name(System.String)}

Solution

  • It's the excel file itself. Above methods work fine with an empty excel file (xlsx)