Problem with error trapping

[Michel S.]

Hi !

Can anybody help me with the following "strange" error trapping
situation ?

I have an ADODB.Connection set to access an Excel file.

Part of code is :

With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & strFileName & _
";Extended Properties='Excel 8.0;Hdr=Yes'"
.CursorLocation = adUseClient
.Open
End With

If the strFileName isn't a valid Excel file, the above code fails on
the .Open line with an error 0x80004005 : The external isn't in
expected format.

Ok..

To take care of this, I modified the code for the following :

(.Open removed from With..End With structure)

On Error Resume Next
cnnExcel.open
MsgBox Err.Number
On Error goto 0

As expected, the MsgBox pops with the decimal value corresponding to
80004005 hex.

Ok.. But since the function is supposed to do something else than just
popping an message, I replaced the MsgBox with the following :

On Error Resume Next
cnnExcel.open
lngErr = Err.Number
On Error goto 0

If lngErr <> 0 Then
MsgBox "The file is not a valid Excel file."
Else
Set rstExcel = .....

To my dismay, right after the assignment, the lngErr value is 0.

As a consequence, the Else part of the If statement is executed, and,
as expected, the Set rst crashes with an error 3704 "Unauthorized
operation when object is closed" (my translation of the actual
message).


Why, oh why, this lngErr = Err.Number fails ?? I saw many code
samples where the assignment of the Err.Number to a variable is used..

And how should this be coded to work correctly ? If possible, I'd
like to avoid to use the "If Err.Number <> 0 Then" because I want to
disable the On Error Resume Next ASAP.


Thanks in advance for any help on this !

PS: I'm using Access 2002.

 

[Michel S.]

Update to my previous post :

I made the changes below and it now do what I want.

But even if it does the job, I still doesn't understand why the simple
assignment lngErr = Err.Number fails.

If anybody has an explanation, I'm eager to read it ! ;o)


' Modified code

On Error Resume Next
cnnExcel.open
If Err.Number <> 0 Then
blnOpenFailed = True
End If
On Error GoTo 0

If blnOpenFailed <> 0 Then
MsgBox "The file is not a valid Excel file."
Else
Set rstExcel = .....
....

 

[Smartin]

Hi !

Can anybody help me with the following "strange" error trapping situation ?

I have an ADODB.Connection set to access an Excel file.

Part of code is :

With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & strFileName & _
";Extended Properties='Excel 8.0;Hdr=Yes'"
.CursorLocation = adUseClient
.Open
End With

If the strFileName isn't a valid Excel file, the above code fails on the
.Open line with an error 0x80004005 : The external isn't in expected
format.

Ok..

To take care of this, I modified the code for the following :

(.Open removed from With..End With structure)

On Error Resume Next
cnnExcel.open
MsgBox Err.Number
On Error goto 0

As expected, the MsgBox pops with the decimal value corresponding to
80004005 hex.

Ok.. But since the function is supposed to do something else than just
popping an message, I replaced the MsgBox with the following :

On Error Resume Next
cnnExcel.open
lngErr = Err.Number
On Error goto 0

If lngErr <> 0 Then
MsgBox "The file is not a valid Excel file."
Else
Set rstExcel = .....

To my dismay, right after the assignment, the lngErr value is 0.

As a consequence, the Else part of the If statement is executed, and, as
expected, the Set rst crashes with an error 3704 "Unauthorized operation
when object is closed" (my translation of the actual message).


Why, oh why, this lngErr = Err.Number fails ?? I saw many code samples
where the assignment of the Err.Number to a variable is used..

And how should this be coded to work correctly ? If possible, I'd like
to avoid to use the "If Err.Number <> 0 Then" because I want to disable
the On Error Resume Next ASAP.


Thanks in advance for any help on this !

PS: I'm using Access 2002.

I'm thinking the Err object doesn't exist until the error is thrown.

This won't do exactly what you want but try it to see how errors can be
handled:

On Error Goto ErrorHandler
cnnExcel.open
<code>
Exit Sub

ErrorHandler:
lngErr = Err.Number
Msgbox lngErr
Resume Next

End Sub