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.Applicati on, 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.Cou nt
str1 = Ctype(rng(i, j), Excel.Range).Va lue.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.Cou nt
Try
str1 = Ctype(rng(i, j), Excel.Range).Va lue.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).va lue 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==?=]

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

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.Applicati on, 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.Cou nt
str1 = Ctype(rng(i, j), Excel.Range).Va lue.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.Cou nt
Try
str1 = Ctype(rng(i, j), Excel.Range).Va lue.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).va lue 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]

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

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" <Rich@discussio ns.microsoft.co mwrote in message
news:3E0F2E29-171E-40CF-9292-1ADEABB1B6C2@mi crosoft.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.Applicati on, 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.Cou nt
> str1 = Ctype(rng(i, j), Excel.Range).Va lue.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.Cou nt
> Try
> str1 = Ctype(rng(i, j), Excel.Range).Va lue.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).va lue 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.]

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

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.Cou nt
str1 = Ctype(rng(i, j), Excel.Range).Va lue.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==?=]

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

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.Cou nt
str1 = Ctype(rng(i, j), Excel.Range).Va lue.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.
>