How to retrieve the list of named ranges in an Excel workbook?

24 Jun 20263 minutes to read

The following code examples demonstrate retrieving the list of named ranges in an Excel workbook using C# (Cross-platform and Windows-specific) and VB.NET.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/Input.xlsx"));
    IWorksheet worksheet = workbook.Worksheets[0];

    //Retrieving names defined in the workbook 
    IName[] names = new IName[workbook.Names.Count];
    for (int i = 0; i < workbook.Names.Count; i++)
    {
        names[i] = workbook.Names[i];
        Console.WriteLine(names[i].Name);
    }

    //Saving the workbook
    workbook.SaveAs(Path.GetFullPath(@"Output/Output.xlsx"));
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("Input.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

    //Retrieving names defined in the workbook 
    IName[] names = new IName[workbook.Names.Count];
    for (int i = 0; i < workbook.Names.Count; i++)
    {
        names[i] = workbook.Names[i];
        Console.WriteLine(names[i].Name);
    }

    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
    ' Instantiate the Excel application object
    Dim application As IApplication = excelEngine.Excel

    ' Set the default application version
    application.DefaultVersion = ExcelVersion.Xlsx

    ' Load the existing Excel workbook into IWorkbook
    Dim workbook As IWorkbook = application.Workbooks.Open("Input.xlsx")

    ' Get the first worksheet in the workbook into IWorksheet
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    ' Retrieving names defined in the workbook
    Dim names(workbook.Names.Count - 1) As IName
    For i As Integer = 0 To workbook.Names.Count - 1
        names(i) = workbook.Names(i)
        Console.WriteLine(names(i).Name)
    Next

    ' Saving the workbook
    workbook.SaveAs("Output.xlsx")
End Using

A complete working example in C# is present on this GitHub page.