readout of cell names from Excel

[tobber99]

Hi,

can anyone tell me, if it is possible to read out the cell name from an excel sheet with C#. If this is possible, how can I realize that?

Thanks,
Tobias

 

[Mike_R]

Well, basic Excel Automation would look something like the following. It (1) Creates a New instance of Excel.Application, (2) opens the Workbook found at "C:\My Documents\Book1.xls", and then (3) accesses Sheet1.Range("A1") and reads off its (a) .Address, (b) .Value and (c) .Text:

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

class ExcelAutomate
    {
        public static void RunExcel()
        {
            const string wbName = "C:\\My Documents\\Book1.xls";

            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWB = xlApp.Workbooks.Open(wbName, 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);
            Excel.Worksheet xlWS = (Excel.Worksheet)xlWB.Worksheets.get_Item("Sheet1");
            Excel.Range rng = xlWS.get_Range("A1", Type.Missing);

            // Show Excel to the User:
            xlApp.Visible = true;

            // Return the Adddress:
            MessageBox.Show(rng.get_Address(Type.Missing, Type.Missing,
                                            Excel.XlReferenceStyle.xlA1 , 
                                            Type.Missing, Type.Missing));
            // Return the Value held:
            MessageBox.Show(rng.get_Value(Type.Missing).ToString());

            // Return the Text as formatted in the Cell:
            MessageBox.Show(rng.Text.ToString());

            // Close down Excel cleanly:
            rng = null;
            xlWS = null;
            xlWB.Close(false, System.Type.Missing, System.Type.Missing);
            xlApp.Quit();
            xlApp = null;
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
    }

The above is a little bit of a hassle because C# cannot handle Optional Parameters nor Properties that take a Parameter. MS Office, using COM, utilizes both, and so C# needs to use a lot of System.Type.Missing values in its Method calls and needs to use Property replacements provided by the PIA such as Excel.Range.get_Value() Method instead of the Excel.Range.Value() Property, which C# cannot use.

VB.Net can handle this stuff natively, so its code looks a little smoother when Automating MS Office Apps. I have a tutorial here (written in VB) that explains the basics:Automating Office Programs with VB.Net / COM Interop. Gen Getz gets into .Net Excel Automation in detail, discussing it from both a VB and C# perspective: Excel Object Model from a .NET Developers Perspective.

Hope this helps! Let us know if anything here was unclear...

,
Mike

 

[tobber99]

Thanks Mike for your help, but your answer doesn`t solve my problem. I need the myself defined name of the cell (please see picture).

Furthermore the object range doesn`t have a method like get_value. Perhaps it is foundet, that I work with the office version 2000.

Thanks again,

Tobi

 

[Mike_R]

Hi Tobi,

To get a Named Range like "cellname", you need to know if its a Workbook-defined Name or a Worksheet-Defined name. If its on the Worksheet, you can get the Range as follows:

Excel.Range rng = xlWS.get_Range("cellname");

Or you could acccess the Worksheet.Names collection like this:

Excel.Range Rng = xlWS.Names.Item("cellname").RefersToRange;

Most Range Names, however, are at the Workbook-level, accessible by all the Worksheets. In this case, you need to access the Workbook.Names collection:

Excel.Range rng = xlWB.Names.Item("cellname").RefersToRange;

Here are a couple of links to understand Range Names better:

(1) Excel Range Name Times (ExcelTip)
(2) Working With Named Ranges In Excel (CPearson)


As for why you do not have a Range.get_Value() method, I dont see how that is possible... Are you sure?? Do you have a Worksheet.get_Range() method?

-- Mike

 

[tobber99]

Hi Mike,

sorry, my problem does not lie in the selection of the cell or range value, but in the identification of the cell name in the excel sheet. I cannot access the range, without the cell name. I would like to appraise of the cell name.

Thanks, Tobi

 

[Mike_R]

You can enumerate through the Workbook.Names collection to get all the Workbook-level names. You can enumerate through the Worksheet.Names collection to get all the names on a given Worksheet.

The other two links above should be of value (that is, I would read them), but based on your needs, maybe this one is even better for you:

Working With Names in VBA (J-Walk)

I hope this helps...

-- Mike

 

[tobber99]

Thanks

Thanks Mike, that was the answer for my question