' How to Read Line By Line & Retrieve Each Word From Text File
Option Explicit
Private Sub Form_Load()
' An example of using the ImportDelimitedFile routine
Dim values() As Variant, i As Long, j As Long
values() = ImportDelimitedFile("c:\temp\sri.txt", " ")
' Values(0)(n) is the name of the Nth field.
' Values(i)(n) is the value of the Nth field on the ith record.
' For example, see how you can increment employees' salaries by 20%.
For i = 0 To UBound(values)
For j = 0 To UBound(values(i))
Debug.Print values(i)(j)
Next
Next
End Sub
Function ReadTextFileContents(filename As String) As String
Dim fnum As Integer, isOpen As Boolean
On Error GoTo Error_Handler
' Get the next free file number.
fnum = FreeFile()
Open filename For Input As #fnum
' If execution flow got here, the file has been open without error.
isOpen = True
' Read the entire contents in one single operation.
ReadTextFileContents = Input(LOF(fnum), fnum)
' Intentionally flow into the error handler to close the file.
Error_Handler:
' Raise the error (if any), but first close the file.
If isOpen Then Close #fnum
If Err Then Err.Raise Err.Number, , Err.Description
End Function
' Load a text file into a TextBox control.
'Text1.Text = ReadTextFileContents("c:\bootlog.txt")
Function ImportDelimitedFile(filename As String, Optional delimiter As String = vbTab) As Variant()
Dim lines() As String, i As Long
' Get all lines in the file.
lines() = Split(ReadTextFileContents(filename), vbCrLf)
' To quickly delete all empty lines, load them with a special char.
For i = 0 To UBound(lines)
If Len(lines(i)) = 0 Then lines(i) = vbNullChar
Next
' Then use the Filter function to delete these lines.
lines() = Filter(lines(), vbNullChar, False)
' Create a string array out of each line of text
' and store it in a Variant element.
ReDim values(0 To UBound(lines)) As Variant
For i = 0 To UBound(lines)
values(i) = Split(lines(i), delimiter)
Next
ImportDelimitedFile = values()
End Function