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