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