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

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

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.