Get Range Name

[reggme]

Hi all,

 

I'm writing a small tool in c# that need to read data from a given Excel table. Each column header is assigned with a name as a Named Range. And I need to read thru these names to know which information are stored in each columns.

 

For example in $AR$132, I have a range namely "Txn\10_1\TxnDT", RefersTo: Transaction!$AR$132:$AR$132 which refer to transaction date; In $AS$132, named "Txn\10_1\Txntype", RefersTo: Transaction!$AS$132 which refer to transaction type. And there is another named range which tells the range of all columns. eg. "Txn\10_1\headers" ReferTo $AR$132:$AY$132.

 

I wrote the following code to loop thru the headers and read the columns

 

Excel.Application excel;
           Excel.Workbook wb;
           Excel.Worksheet ws;
           object missing = System.Reflection.Missing.Value;

           excel = new Excel.Application();
           wb = excel.Workbooks.Open(args[0], Type.Missing, Type.Missing,      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

           try
           {
               ws = (Excel.Worksheet)wb.Worksheets.get_Item("Sheet1");
               Excel.Range headerRange;
               headerRange = wb.Names.Item("Txn\\10_1\\headers").RefersToRange;
               foreach (Excel.Range col in headerRange.Columns)
               {
                   int i = 1;
                   Console.WriteLine("[" + i.ToString() + "] " + col.Cells[1, i].Value.ToString() + " = " + GetName(col.Cells[1, i]));
                   i++;
               }
           }
           catch (System.Runtime.InteropServices.COMException e)
           {
               Console.WriteLine(e.Message);
           }
           finally
           {                
               ws = null;
               wb.Close(false, Type.Missing, Type.Missing);
               excel.Quit();
               excel = null;
               GC.Collect();
               GC.WaitForPendingFinalizers();
               Console.WriteLine("Press any key to terminate...");
               Console.ReadLine();
           }

       public static string GetName(Excel.Range range)
       {
           try
           {
               if (range.Name != null)
               {
                   Excel.Name name = range.Name as Excel.Name;

                   if (name.Name != null || name.Name.Length != 0)
                   {
                       return name.Name;
                   }
                   return string.Empty;
               }
               return string.Empty;
           }
           catch (Exception e)
           {
               return string.Empty;
           }
       }

 

The code works fine if the range.ReferTo only specified with a single cell, eg. $AS$132 but fails if range.ReferTo is $AR$132:$AR$132. Even though they both are refer to a single cell.

 

How can I get the range name of $AR$132:$AR$132?

 

Thank you in advance.