CODEWITHSUNDEEP
vbapowerpoint
HotDogCannon
HotDogCannon

Reputation: 2386

Reading in data from text file into a VBA array

I have the following VBA code:

Sub read_in_data_from_txt_file()

Dim dataArray() As String
Dim i As Integer

Const strFileName As String = "Z:\sample_text.txt"
Open strFileName For Input As #1

' -------- read from txt file to dataArrayay -------- '

i = 0
Do Until EOF(1)
    ReDim Preserve dataArray(i)
    Line Input #1, dataArray(i)
    i = i + 1
Loop
Close #1

Debug.Print UBound(dataArray())

End Sub

I'm trying to read in text line by line (assume 'sample.txt' is a regular ascii file) from a file and assign this data to consecutive elements in an array.

When I run this, I get all my data in the first value of the array.

For example, if 'sample.txt' is:

foo
bar
...
dog
cat

I want each one of these words in a consecutive array element.

Upvotes: 4

Views: 21938

Answers (2)

Adam Bouras
Adam Bouras

Reputation: 11

Here is a clean code on how to use for each loop in VBA

Function TxtParse(ByVal FileName As String) As String
    Dim fs, ts As Object
    Dim strdic() As String
    Dim oitem As Variant
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set ts = fs.OpenTextFile(FileName, 1, False, -2)

    strdic = Split(ts.ReadAll, vbLf)

    For Each oitem In strdic
        If InStr(oitem, "YourString") <> 0 Then
        Else
            If InStr(1, oitem, vbTab) <> 0 Then
                    Debug.Print "Line number is : "; "'" & Replace(oitem, vbTab, "','") & "'"
            Else
                    Debug.Print "Line number is : "; "'" & Replace(oitem, ",", "','") & "'"
            End If
        End If
    Next
End Function

Upvotes: 1

Alex K.
Alex K.

Reputation: 175826

What you have is fine; if everything ends up in dataArray(0) then the lines in the file are not using a CrLf delimiter so line input is grabbing everything.

Instead;

open strFileName for Input as #1
dataArray = split(input$(LOF(1), #1), vbLf)
close #1

Assuming the delimiter is VbLf (what it would be coming from a *nix system)

Upvotes: 7

Related Questions