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 it's (a) .Address, (b) .Value and © .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 Developer's Perspective.

 

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

 

:),

Mike

Edited February 3, 2005 by Mike_R

[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

Edited February 4, 2005 by tobber99

[Mike_R]

Hi Tobi,

 

To get a Named Range like "cellname", you need to know if it's a Workbook-defined Name or a Worksheet-Defined name. If it's 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 don't 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