VBA/Excel/Access/Word/File Path/TextStream
On Error/Goto and the Err Object
<source lang="vb">
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
</source>
Read text file
<source lang="vb">
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
</source>
The TextStream Object
<source lang="vb">
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
</source>