If 'undefined' then ... VS Try / Catch - how to trap 'undefined

[=?Utf-8?B?UmljaA==?=]

I have to read data from an Excel Sheet. Using Microsoft Office Interop and
Automation I create an Excel object

Dim xl As New Excel.Application, wkbk As Excel.Workbook, rng as Excel.Range

I open a workbook and then read the data from a worksheet using the Sheet's
UsedRange property into an Excel Range object I call rng. Then I loop
through all the cells in this rng object. This works fine except if there is
no data in a particular cell.

Try
....
For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Count
str1 = Ctype(rng(i, j), Excel.Range).Value.ToString
Next
Next
....
Catch ex1 As Exception
....
End Try

The loop aboves works fine if there is data in a given cell/range. But
bombs if there is no data (crashes the app) even with the Try/Catch. So I
added another Try/Catch inside the loop

For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Count
Try
str1 = Ctype(rng(i, j), Excel.Range).Value.ToString
...
Catch ex As Except
str1 = "**"
...
End Try
Next
Next

In the system error trap (after the app crashed without the inner
try/catch) the message said the value which crashed the app was 'undefined'.
I tried

If ctype(rng(i, j), Excel.Range).value Is Nothing Then ...

but this did not keep the app from crashing. Is there a way to trap for
'Undefined' besides Try/Catch or is Try/Catch the desired method?

Thanks,
Rich

[=?Utf-8?B?UmljaA==?=]

I forgot to mention that the Inner Try/Catch did keep the app from crashing.
So the inner Try/Catch did work! My question is if there is a way to trap
for the 'Undefined' value instead of having to use an Inner Try/Catch block.

"Rich" wrote:

I have to read data from an Excel Sheet. Using Microsoft Office Interop and
Automation I create an Excel object

Dim xl As New Excel.Application, wkbk As Excel.Workbook, rng as Excel.Range

I open a workbook and then read the data from a worksheet using the Sheet's
UsedRange property into an Excel Range object I call rng. Then I loop
through all the cells in this rng object. This works fine except if there is
no data in a particular cell.

Try
...
For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Count
str1 = Ctype(rng(i, j), Excel.Range).Value.ToString
Next
Next
...
Catch ex1 As Exception
...
End Try

The loop aboves works fine if there is data in a given cell/range. But
bombs if there is no data (crashes the app) even with the Try/Catch. So I
added another Try/Catch inside the loop

For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Count
Try
str1 = Ctype(rng(i, j), Excel.Range).Value.ToString
...
Catch ex As Except
str1 = "**"
...
End Try
Next
Next

In the system error trap (after the app crashed without the inner
try/catch) the message said the value which crashed the app was 'undefined'.
I tried

If ctype(rng(i, j), Excel.Range).value Is Nothing Then ...

but this did not keep the app from crashing. Is there a way to trap for
'Undefined' besides Try/Catch or is Try/Catch the desired method?

Thanks,
Rich

[Miro]

I think you have to use the inner try catch. I may be a bit of a newbie,
but otherwise if you do not use the "inside try catch" by my opinion, your
for next loops will end instantly.

Using the inside for next loops, traps the 1 cell that has errors but allows
the other cells to continue to import.

Miro

"Rich" <Ri**@discussions.microsoft.comwrote in message
news:3E**********************************@microsof t.com...

>I forgot to mention that the Inner Try/Catch did keep the app from
crashing.
So the inner Try/Catch did work! My question is if there is a way to
trap
for the 'Undefined' value instead of having to use an Inner Try/Catch
block.

"Rich" wrote:

>I have to read data from an Excel Sheet. Using Microsoft Office Interop
and
Automation I create an Excel object

Dim xl As New Excel.Application, wkbk As Excel.Workbook, rng as
Excel.Range

I open a workbook and then read the data from a worksheet using the
Sheet's
UsedRange property into an Excel Range object I call rng. Then I loop
through all the cells in this rng object. This works fine except if
there is
no data in a particular cell.

Try
...
For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Count
str1 = Ctype(rng(i, j), Excel.Range).Value.ToString
Next
Next
...
Catch ex1 As Exception
...
End Try

The loop aboves works fine if there is data in a given cell/range. But
bombs if there is no data (crashes the app) even with the Try/Catch. So
I
added another Try/Catch inside the loop

For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Count
Try
str1 = Ctype(rng(i, j), Excel.Range).Value.ToString
...
Catch ex As Except
str1 = "**"
...
End Try
Next
Next

In the system error trap (after the app crashed without the inner
try/catch) the message said the value which crashed the app was
'undefined'.
I tried

If ctype(rng(i, j), Excel.Range).value Is Nothing Then ...

but this did not keep the app from crashing. Is there a way to trap for
'Undefined' besides Try/Catch or is Try/Catch the desired method?

Thanks,
Rich

[Phill W.]

Rich wrote:

I open a workbook and then read the data from a worksheet using the Sheet's
UsedRange property into an Excel Range object I call rng. Then I loop
through all the cells in this rng object. This works fine except if there is
no data in a particular cell.

For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Count
str1 = Ctype(rng(i, j), Excel.Range).Value.ToString

What other properties/methods does the Range object support?
Is there one that would indicate the lack of a value?

From the /little/ I remember of Excel's VBA, doesn't .FormulaR1C1 come
back as an empty string?

HTH,
Phill W.

[=?Utf-8?B?UmljaA==?=]

Not completely sure about .FormulaR1C1

I will have to try it out in Excel and see what I get. Might be an idea.
But you gave me another idea - this may be more an Excel Question than a
vb.net question. Maybe an Excel person has some suggestions. I will have to
try that NG.

Thanks all for your replies.

"Phill W." wrote:

Rich wrote:

I open a workbook and then read the data from a worksheet using the Sheet's
UsedRange property into an Excel Range object I call rng. Then I loop
through all the cells in this rng object. This works fine except if there is
no data in a particular cell.

For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Count
str1 = Ctype(rng(i, j), Excel.Range).Value.ToString

What other properties/methods does the Range object support?
Is there one that would indicate the lack of a value?

From the /little/ I remember of Excel's VBA, doesn't .FormulaR1C1 come
back as an empty string?

HTH,
Phill W.