CODEWITHSUNDEEP
arraysvba
walkens
walkens

Reputation: 59

How to fetch text file into array in VBA

I'm trying to fetch the tab delimited text file into arrays, I already know how to read that text file into spreadsheet, the following is my code which works perfectly:

While Not EOF(iFile)
        Line Input #iFile, LineText
            Dim arr
            arr = Split(CStr(LineText), vbTab)
            For j = 1 To UBound(arr)
                Worksheets("TxtRead").Cells(i, j).Value = arr(j - 1)
            Next

            i = i + 1
    Wend
    Close #iFile

So instead of fetching values to the spreadsheet, I would like to write them to a two-dimentional array, how would I do that? I have a code below, but it doesn't work:

Dim MemoryArray()
    While Not EOF(iFile)
        Line Input #iFile, LineText
            Dim arr
            arr = Split(CStr(LineText), vbTab)
            For j = 1 To UBound(arr)
                Worksheets("TxtRead").Cells(i, j).Value = arr(j - 1)
                MemoryArray(i - 1, j - 1) = arr(j - 1)
            Next

            i = i + 1
    Wend
    Close #iFile

Thanks for any inputs and thoughts!

Upvotes: 2

Views: 3646

Answers (1)

Tim Williams
Tim Williams

Reputation: 166351

Sub Tester()

    Dim arr

    arr = FileToArray("D:\Stuff\test.txt")

    Debug.Print arr(1, 1), arr(10, 10) 'print some values

End Sub



Function FileToArray(fpath) As Variant

    Dim txt As String, arr, d, r, c, rv(), u

    'read in the entire file
    With CreateObject("scripting.filesystemobject").opentextfile(fpath)
        txt = .readall()
        .Close
    End With

    arr = Split(txt, vbCrLf) 'split lines to an array

    u = UBound(Split(arr(0), vbTab)) 'assume all lines have same # of fields
    ReDim rv(1 To UBound(arr) + 1, 1 To u + 1) 'size the output array

    'fill the output array
    For r = 0 To UBound(arr)
        d = Split(arr(r), vbTab)
        For c = 0 To u
            rv(r + 1, c + 1) = d(c)
        Next c
    Next r

    FileToArray = rv

End Function

Upvotes: 2

Related Questions