Where to place On Error

[Rob]

Hi,

I'm using the following code to open a text file and carry out minor changes
within Excel. However, there have been a couple of occassions where a
Run-Time error has occured and as such I need to trap the error and report
that the user should start over again.

Where would I place the code and what would it look like? I belive I need
On Error GoTo ErrorHandler and ErrorHandler: Exit Sub but where does this go
in the code below to trap any error but still allow the routine to run and
display the Successful process message.

Thanks, Robert

Sub ImportFile()
Dim WkBk As Workbook
Dim fName As Variant
Dim iFname As String
iFname = Application.ActiveWorkbook.Name
fName = Application.GetOpenFilename("Text files (*.txt),*.txt", , "Open
File")
Application.ScreenUpdating = False
If TypeName(fName) = "Boolean" Then
MsgBox "You have chosen to cancel the process!", vbExclamation,
"Information"
Application.ScreenUpdating = True
Exit Sub
End If
'Open the chosen file using the text import options below
Workbooks.OpenText filename:=fName, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=True, OtherChar:="^", FieldInfo:=
_
Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 4), Array(5,
2), Array(6, 1), Array(7 _
, 9), Array(8, 9))
Columns("A").Columns.AutoFit
Range("C1").NumberFormat = "General"
Range("C1").Formula = _
"=VLOOKUP(LEFT(A1,3)," + iFname + "!Database,2,FALSE)&"" ""&A1&"" W/E:
""&Text(B1,""dd-mmm-yyy"")"
Range("C1").Copy
Range("C1").PasteSpecial Paste:=xlValues
Range("C1").Font.Bold = True
Application.CutCopyMode = False
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
Selection.EntireRow.Delete
Range("A1").Select
Columns("A:B").EntireColumn.Delete
Application.ScreenUpdating = True
MsgBox "Successful process.", vbExclamation, "Information"
End Sub

 

[JY]

Hi Rob
Like this

Sub Test
on error goto myErrorHandler
do mystuff
exit sub
myErrorHandler :
if error.description = .;;;; then
end sub
Regards,
JY

 

[JY]

Hi
Look also in help for "On error"
HTH
Regards
JY

 

[Rob]

Thanks JY.

I'll preserver with this error handle and hopefully get there, I think I'm
putting the code to go to when there is an error in the wrong place as when
I generate an error, I still see the successful process message.

Thanks, Rob