Get Range Name

R

reggme

Newcomer
Joined
Aug 1, 2011
Messages
1
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

Code: 
 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.
 
Back
Top