4

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
HotDogCannon
  • 2,113
  • 11
  • 34
  • 53
  • 1
    Ok, read [**this**](http://vba4all.wordpress.com/category/vba-macros/reading-txt-files-from-vba/). You may want to `.readLine` and then `Split(.readLine, " ")` –  May 27 '14 at 10:35
  • Your code seems to be working fine. What does the line `Debug.Print UBound(dataArray())` give you? – Olle Sjögren May 27 '14 at 10:46
  • You're opening for `Input`. Have you tried opening for `Output`? – RubberDuck May 27 '14 at 11:21

2 Answers2

7

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)

Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Would using the vbLf constant work for Linux/Unix, Mac and Windows OSes? So if I have a text file that has Item1 Item2 Item3 Regardless of which OS created the file it would be able to be read into an array – psycoperl Apr 11 '23 at 19:43
0

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
Adam Bouras
  • 1
  • 3