Trouble Copying from txt file to Excel - pastes into one cell only

[jbuist]

Hello. I'm trying to paste from a txt file into Excel. I can successfully paste into ONE cell, but I need it to paste into multiple cells.

E.g. the txt file looks like this:
1
2
3
4
5
6

With my code below (admittedly pilfered from someone else!), it pastes into A1, but it pastes ALL data into A1 delimited by a carriage return. I want it to paste into A1 (1), A2 (2), A3 (3) respectively.

Sub CopyTextFile()
Dim oFso: Set oFso = CreateObject("Scripting.FileSystemObject")
Dim oFile: Set oFile = oFso.OpenTextFile("D:\Spares\Inventory\list_of_spares.txt", 1)
Dim sText
sText = oFile.ReadAll
oFile.Close
ThisWorkbook.Sheets(1).Range("A1").Value = sText
End Sub

Thanks in advance!

 

[John_w]

Try this:

Sub CopyTextFile()
Dim oFso: Set oFso = CreateObject("Scripting.FileSystemObject")
Dim oFile: Set oFile = oFso.OpenTextFile("D:\Spares\Inventory\list_of_spares.txt", 1)
Dim lines As Variant
lines = Split(oFile.ReadAll, vbCrLf)
oFile.Close
ThisWorkbook.Sheets(1).Range("A1").Resize(UBound(lines)).Value = Application.Transpose(lines)
End Sub

 

[jbuist]

It worked! Thank you so much!!