Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read Excel data from VB.net using range object - prob with 'undefi
Hi,
I need to read data from hundreds of Excel files (which are not consistently formatted - # of sheets vary, UsedRange per sheet varies...) and write some of that data to a sql server. I put together a quicky VB.Net app to do this. I use the range object with automation from the VB.Net app (VB2005). ....Dim rng As Excel.Range, wkbk As Excel.WorkBook wkbk = xl.Workbooks.Open("C:\...") For Each sht As Excel.WorkSheet In wkbk.Sheets rng = sht.UsedRange For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count Try str1 = Ctype(rng(i, j), Excel.Range).Value.ToString Catch Ex As Exception End Try Next Next .... This routine works fine except for a little overhead from the Inner Try Catch Block in the For Loop (I also had to use Ctype since VB2005 is OOP). A problem I was having was that in the UsedRange - some cells were empty and my vb.net app was crashing with a system error message that the crashing value was 'Undefined'. So I added the Inner Try Catch block. This fixed the problem - but has added a little bit of overhead. Someone from the vb.net group suggested to test values using rng.FormulaR1C1. I have not tried that yet, but wasn't sure if this was a way (or the way) to go. So I am checking if anyone in this Excel group works with Excel and .Net (VB/C# either or) and might have another suggestion or could confirm about using .FormulaR1C1 to test a value - the goal would be to return an empty string instead of an 'Undefined' value. Thanks, Rich |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Range.ServerActions Property displays read-only data | Excel Programming | |||
Prob with ChartSource DataData Range | Charts and Charting in Excel | |||
Path name in title bar-READ ONLY prob | Excel Programming | |||
Possible to read data entered in a worksheet's Form object, using a VBA command? | Excel Programming | |||
Excel VBA - Read and modify the chart data range | Excel Programming |