Search code examples
listrangeexcel-2002

Is it possible to return a list of all ranges from all worksheets in an Excel 2002 workbook?

I want to extract "special" data from an Excel 2002 (client requirement, cannot change) workbook and worksheets contained therein. I have classified ranges in this "special" data category. I would like to acquire a list of all ranges in, ideally, all worksheets in a workbook. The attributes I'm interested in are the range name, and the range address. I have been googling for a while now, and have not found anything relevant.

I was assuming the Excel 2002 API would expose something like this:

ApplicationClass app = new ApplicationClass();
Workbook workbook = app.Workbooks.Open(@"c:\file.xls", ...);
Worksheet worksheet = workbook.Worksheets["sheet1"] as Worksheet;
Range[] ranges = worksheet.GetAllRanges();

or something similar. However, I am sadly mistaken.

Is this possible with Excel 2002?

Solution

  • If you are looking for all the ranges that are Named you should look at the Workbook.Names collection. Each Name in the Names collection has a RefersTo property that gives the Formula or Range Reference that is referenced by the Name. (But be aware that a Name can be a Formula rather than a Range Reference)
    Dim oNM as Name

    for each oNM in ActiveWorkbook.Names
    debug.print oNM.Refersto
    next oNM