Repeat error trapping when file is not found.

[mikeburg]

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

[Jim Thomlinson]

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
>
>

[mikeburg]

Thanks a million.

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