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.

Answer 1

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)