On Error/Goto and the Err Object : TextStream « File Path « VBA / Excel / Access / Word






On Error/Goto and the Err Object

 
Private Sub ReadTxtFile()
    Dim myFileSystemObject As FileSystemObject, aFile As TextStream
    Dim fileContent As String, colStr As String
    Dim I As Integer, ms As Integer
    Dim fPath As String

    On Error GoTo ErrorHandler
    I = ActiveCell.Row
    Set myFileSystemObject = New FileSystemObject
    fPath = "D:\myFile.txt"
    Set aFile = myFileSystemObject.OpenTextFile(fPath, 1)
    Do While Not aFile.AtEndOfStream
        fileContent = aFile.ReadLine
        Debug.Print fileContent
    Loop
    aFile.Close     'Close file and convert lines of data to columns
    Set myFileSystemObject = Nothing
    Set aFile = Nothing

    Exit Sub
ErrorHandler:
    If Err.Number = 53 Or Err.Number = 76 Then
        ms = MsgBox(Err.Description & vbCrLf & _
                  "Do you want to look for the file?", vbYesNo)
        If ms = 6 Then
            fPath = ShowFileDialog(fPath)
            If fPath <> "Cancelled" Then Resume
        Else
            MsgBox "Resolve file error before continuing."
        End If
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
End Sub
Private Function ShowFileDialog(fName As String) As String
    Dim fd As FileDialog
    Dim I As Integer
    Set fd = Application.FileDialog(msoFileDialogOpen)
    With fd
        .AllowMultiSelect = False
        .FilterIndex = 2
        .Title = "Find File"
        .InitialFileName = fName
        If .Show = -1 Then
            ShowFileDialog = .SelectedItems(1)
        Else
            ShowFileDialog = "Cancelled"
        End If
    End With
    Set fd = Nothing
End Function

 








Related examples in the same category

1.The TextStream Object
2.Read text file