Reading formatted cell values into an array
 

You gave a pretty good summary of the situation.

--
Regards,
Tom Ogilvy


Dan Stine wrote in message
...
Hello,

I am using VB.NET to read the contents of a range of cells (such as
A1:E100)
from Excel into an array. What I want returned to me are the formatted
values,
the contents that are actually displayed to the user when s/he looks at
the
worksheet in Excel. So if a cell were defined by a formula such as "=1/3"
and
the format was percentage, I want to see "33%" as the value in VB.NET and
NOT
the underlying "0.333333333".

I would like to read all the formatted values for the range in a single
call
to
the Excel.Range object for maximum efficiency. I find that Excel accesses
are
expensive in time. However, I cannot find the property of the Excel.Range
object that will read in an array of formatted values.

The .Text property of Excel.Range returns System.DBNull when operating on
a
multi-celled Excel.Range:

Dim range As Excel.Range = worksheet.Range("E3", "M85")
Dim value As Object = range.Text
' value = System.DBNull

The .Value property of Excel.Range returns an array of unformatted
content:

Dim range As Excel.Range = worksheet.Range("E3", "M85")
Dim array As Array = CType(range.Value, Array)
Dim value As String = array.GetValue(2,2)
' value = "0.333333333", not "33%"

I tried defining my array as Strings, in the hope that an implicit cast
from
Double to String would give me the results I'm looking for:

' srcWks is an Excel.Worksheet
Dim range As Excel.Range = worksheet.Range("E3", "M85")
Dim array() As String
array = CType(range.Value, String())
' throws runtime invalid cast exception

So my question is, does there exist a way to read in the contents of an
Excel.Range object to an array as formatted data?

I thought about trying to read the cell formats into a parallel array, and
then
apply them myself in VB.NET. But I could not figure out how to do that.

Thanks for any help,
Dan