THAT WORKS, THANK YOU!!!!!!

"RB Smissaert" wrote:

Or to make it simpler you can put it all in one Sub:

Sub TextFileToColum(strFile As String, rngFirstCell As Range)

Dim i As Long
Dim str As String
Dim arr
Dim arr2

str = TextFileToString(strFile)
arr = Split(str, ",")
ReDim arr2(1 To UBound(arr) + 1, 1 To 1)

For i = 0 To UBound(arr)
arr2(i + 1, 1) = arr(i)
Next i

Range(rngFirstCell, _
Cells(rngFirstCell.Row + UBound(arr2) - 1, _
rngFirstCell.Column)) = arr2

End Sub

Function TextFileToString(ByVal strFile As String) As String

Dim hFile As Long

On Error GoTo ERROROUT

hFile = FreeFile
Open strFile For Binary Access Read As #hFile
TextFileToString = Space(LOF(hFile))
Get hFile, , TextFileToString
Close #hFile

Exit Function
ERROROUT:

If hFile 0 Then
Close #hFile
End If

End Function

You can then run it with this simple code:

Sub test()

TextFileToColum "C:\testfile.txt", Cells(1)

End Sub


This is in VBA, so in Excel press Alt + F11, then press Ctrl + R to see the
VB project explorer, in the left hand pane right-click your VBAProject
and do insert module, then paste the posted code to that module. You can run
the code from the worksheet with Tools, Macro, Macros.


RBS


"Ralph" wrote in message
...
hi,

i have a massive text file that is nothing more than a series of words
separated by commas, i.e.

dog, cat, ball, clouds, rain

there are probably 20k to 30k entries ...

is there a way i can import this file into excel so that these words are
stacked in 1 column?? i am doing it the file-import way but it pulls only
so
many into 1 row, and then i have to copy-paste-transpose, etc... it will
take
me 5 years to do it this way lol

PLEASE HELP!

THANKS!!