VBA/Excel/Access/Word/File Path/TextStream

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

Read text file

 
Public Sub ReadTxtFile()
    Dim myFileSystemObject As FileSystemObject
    Dim aFile As TextStream
    Set myFileSystemObject = New FileSystemObject
    Set aFile = myFileSystemObject.OpenTextFile("C:\myFile.txt", 1)
    Do While Not aFile.AtEndOfStream
        Debug.Print aFile.ReadLine
    Loop
    aFile.Close     "Close file and convert lines of data to columns
    Set myFileSystemObject = Nothing
    Set aFile = Nothing
End Sub

The TextStream Object

 
Sub CreateTxtFile()
    Dim myFileSystemObject As FileSystemObject
    Dim aFile As TextStream
    Set myFileSystemObject = New FileSystemObject
    Set aFile = myFileSystemObject.CreateTextFile("C:\myFile.txt", True)
    aFile.WriteLine
    aFile.Write ("asdf")    "Write tab-delimited text
    aFile.Close         "Close file and clear memory
    Set myFileSystemObject = Nothing
    Set aFile = Nothing
End Sub