Repeat error trapping when file is not found.
 

The following VBA error traping code works great once. However, need
additional VBA code to make it work repeatedly if the file specified is
not found. For example entering 0128 for 0158 then 0185 for 0158 causes
an File not Found Eorror #53.

Sub Auto_open()

Dim strClientNumber As String
Dim dtFileDate As Date

On Error GoTo ErrorTrap
strClientNumber = Application.InputBox("Enter A/R Client Number
(ex: 0158): ")
If strClientNumber = "" Then GoTo Done
If strClientNumber = False Then GoTo Done
dtFileDate = Int(FileDateTime("C:\USR\MIKE\AT" & strClientNumber &
".MB"))

ErrorTrap:
If Err.Number < 0 Then
strClientNumber = _
Application.InputBox("Your file does not exist!" _
& vbCrLf & vbCrLf & "Enter A/R Client Number (ex: 0158): ")
If strClientNumber = "" Then GoTo Done
If strClientNumber = False Then GoTo Done
dtFileDate = Int(FileDateTime("C:\USR\MIKE\AT" _
& strClientNumber & ".MB"))
End If

'More code exists here to import the above file that is found

End Sub

Any & all help is very much appreciated. However, since I am still
learning, please keep the VBA code simple. Thank you, mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=561575

Repeat error trapping when file is not found.
 

The reason that it works only once is that you never resume normal execution
out of your error handler. As soon as an error is generated the code goes to
the error handler and continues in the error handler (as there is no resume
statement) until the end. Once in an error handler, you can not catch any
more errors until you resume normal execution. What you really want to do is
to avoid the error handler completely. By using the "Dir" function you can
determine if the file exists without generating an error. If the file does
not exist then prompt for a correct file name (done in a loop until you get a
goood one of the user decides to cancel).
--
HTH...

Jim Thomlinson


"mikeburg" wrote:


The following VBA error traping code works great once. However, need
additional VBA code to make it work repeatedly if the file specified is
not found. For example entering 0128 for 0158 then 0185 for 0158 causes
an File not Found Eorror #53.

Sub Auto_open()

Dim strClientNumber As String
Dim dtFileDate As Date

On Error GoTo ErrorTrap
strClientNumber = Application.InputBox("Enter A/R Client Number
(ex: 0158): ")
If strClientNumber = "" Then GoTo Done
If strClientNumber = False Then GoTo Done
dtFileDate = Int(FileDateTime("C:\USR\MIKE\AT" & strClientNumber &
".MB"))

ErrorTrap:
If Err.Number < 0 Then
strClientNumber = _
Application.InputBox("Your file does not exist!" _
& vbCrLf & vbCrLf & "Enter A/R Client Number (ex: 0158): ")
If strClientNumber = "" Then GoTo Done
If strClientNumber = False Then GoTo Done
dtFileDate = Int(FileDateTime("C:\USR\MIKE\AT" _
& strClientNumber & ".MB"))
End If

'More code exists here to import the above file that is found

End Sub

Any & all help is very much appreciated. However, since I am still
learning, please keep the VBA code simple. Thank you, mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=561575

Repeat error trapping when file is not found.
 

Thanks a million.

I did as you suggested with Dir & it works great! mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=561575