Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xl2007 - Reading Values and XValues | Charts and Charting in Excel | |||
Reading in poorly formatted text file | Excel Discussion (Misc queries) | |||
line of best fit/reading values from graphs | Excel Discussion (Misc queries) | |||
Converting 'General' formatted cells to Text formatted cell using. | Excel Worksheet Functions | |||
Reading a cell into an array | Excel Programming |