CODEWITHSUNDEEP
excelvsto
Nanda
Nanda

Reputation: 31

Value in a cell from CellReference in C#

I am using Office Excel VSTO. I have a cell reference info of a cell in my workbook. say, Sheet1!$A$5. I want to get the information in this cell and its type. Is this possible in VSTO by any means?
I am now breaking this cell reference going to the sheet and cell get getting the values. I suppose more easy ways are possible.

Upvotes: 2

Views: 998

Answers (2)

Anonymous Type
Anonymous Type

Reputation: 3061

Ok I think I have your question figured out.
You want to use VSTO to replace a workbook link that you are using to update a value in your current workbook.
For me the clues are ....
1. want to get the information in this cell and its type
2. use VSTO to do it
3. am now breaking this cell reference
btw if my assumption above is correct, then please edit your question to make more sense for future readers.

Code Sample 

//get workbook link cell ref  
var range = (Range)Globals.ThisAddIn.Application.Range["Sheet1!$a$5"];  
//determine type  
// if straight linking a value this step is unnecessary unless using the type info to format the cell   
// or because you are doing a transformation or aggregation on the data prior to putting it somewhere.  
// if needed... do some try/catchs on casting it to oledate, bool, double, string in that order.  
// get value
 var value = range.Value2;  
// update "active" sheet  
var sht = (Excel.WorkSheet)Globals.ThisAddIn.Application.ActiveSheet;  
sht.Range["A1"].Value2 = value;  
// don't forget to call FinalReleaseCOMObject and GC.WaitForPendingFinalizers/GC.Collect block!!

Also note that yes, you will be "breaking the cell reference" if you use code INSTEAD. Note you could keep the workbook link, but then there's no point in using the code approach. My advice would be in general use code since it is more flexible, but take advantage of links when you want speed (of configuration) and the data doesn't require more than basic manipulation (SUM, IF, basic math operators).

Upvotes: 0

Mathias
Mathias

Reputation: 15391

I am not sure if this is what you are after, but the following gives you direct access to the cell:

     var range = (Range)Globals.ThisAddIn.Application.Range["Sheet1!$a$5"];
     var cellContent = range.Value2;

Upvotes: 1

Related Questions