Read a text file into an array

Sometimes it's handy to read a text file into an array, for example to process a list of phrases or quotations.

Here's a routine that will do just that and a test routine to demonstrate how it works and how to use it.

Sub TextFileToArray(ByRef aText As Variant, ByVal sTextFile As String)
' Reads a text file into an array, one line per array element

    Dim FileNum As Integer
    Dim Buffer As String
    ReDim aText(1 To 1)

    FileNum = FreeFile()
    Open sTextFile For Input As FreeFile
    While Not EOF(FileNum)
        Line Input #FileNum, Buffer
        ' Ignore blank lines
        If Trim(Buffer) <> "" Then
            'Call AddAPhrase(aText, Buffer)
            aText(UBound(aText)) = Buffer
            ReDim Preserve aText(1 To UBound(aText) + 1)
        End If
    Wend
    Close #FileNum

    ' This leaves the array with one bogus empty record at end so
    ReDim Preserve aText(1 To UBound(aText) - 1) As String

End Sub

And you can use this routine to test the one above:

Sub TestThis()
    Dim sTextFile As String
    Dim aText() As String
    Dim x As Long

    sTextFile = "C:\TEMP\TEXT.TXT"

    Call TextFileToArray(aText, sTextFile)

    For x = LBound(aText) To UBound(aText)
        Debug.Print aText(x)
    Next

End Sub

See How do I use VBA code in PowerPoint? to learn how to use this example code.

---