How can i read a text file line by line and put into excel sheet cell using VBA? Read line is not working

Question

I am using the below code. Instead of putting the data line by line into cells, it is putting all data into one cell. attached image for your reference. Also attached the sample text file that I am reading. please note, when I try to open this text file in Wordpad & save it, then it works fine.

Click here to download sample txt file that is having this issue

 Sub Test_ReadFromTxtToArray()

 Dim FSO As Object, MyFile As Object
 Dim FileName As String, Arr As Variant

 FileName = "C:\Test\O0000540.txt" ' change this to your text file full name
 Set FSO = CreateObject("Scripting.FileSystemObject")
 Set MyFile = FSO.OpenTextFile(FileName, 1)
 Arr = Split(MyFile.ReadAll, vbCrLf) ' Arr is zero-based array


 'For test
 'Fill column A from this Array Arr

 Sheet2.Range("A1").Resize(UBound(Arr) + 1, 1).Value = Application.Transpose(Arr)

End Sub

Why dont you try "Text to columns"? Or use excel option to open a text file and add the delimiter you have. — PerlBatch, Jun 23 '20 at 09:18
Are you sure that EOL is with ? Sorry, but I will not download an unknown file from an unknown source to check. — FunThomas, Jun 23 '20 at 09:35
Yes, EOL is CrLf. you can trust to download this file to check. this my one drive only. — Debaprakash Nayak, Jun 23 '20 at 09:50
the fact that this works after you have re-saved the file in Wordpad makes it almost certain that line terminators not being CRLF is the cause — aucuparia, Jun 23 '20 at 10:14
EOL on the file you made available is `LF` and splitting on that works. — Ron Rosenfeld, Jun 23 '20 at 10:15

score 0 · Accepted Answer · answered Jun 23 '20 at 10:30

It happens that the file you provided for download has LF as the EOL, so your Split method is not splitting anything.

But what if you don't know the EOL?

This should work:

S = MyFile.readall
 
 S = Replace(S, vbCrLf, vbLf)
 S = Replace(S, vbCr, vbLf)

 Arr = Split(S, vbLf)

In other words, change whatever might be there to a known EOL, then split on that. You might need to do something different for the MAC.

How can i read a text file line by line and put into excel sheet cell using VBA? Read line is not working

1 Answers1