Reading in data from text file into a VBA array

Question

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.

Ok, read this. You may want to .readLine and then Split(.readLine, " ") — user2140173
– user2140173, Commented May 27, 2014 at 10:35
Your code seems to be working fine. What does the line Debug.Print UBound(dataArray()) give you? — Olle Sjögren
– Olle Sjögren, Commented May 27, 2014 at 10:46
You're opening for Input. Have you tried opening for Output? — RubberDuck
– RubberDuck, Commented May 27, 2014 at 11:21

Alex K. · Accepted Answer · 2014-05-27 11:10:13Z

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)

answered May 27, 2014 at 11:10

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

psycoperl Over a year ago

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

Adam Bouras · Accepted Answer · 2020-01-28 17:51:48Z

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

Collectives™ on Stack Overflow

Reading in data from text file into a VBA array

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related