You don't need a loop in order to assign the split out values to the column
of cells... you can use the Transpose worksheet function to do it in one
line of code. Here is a subroutine that reads in the file, removes any line
feeds or carriage returns that might be used to separate data neatly in the
file (I added this on the off chance it is needed) and then splits out the
data and assigns it to a column starting at a specified cell address....

Sub TextFileToColumn(PathFilename As String, StartAt As String)
Dim FileNum As Long, TotalFile As String, Arr() As String
' Read entire file into TotalFile variable
FileNum = FreeFile
On Error GoTo Whoops
Open PathFilename For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
' Make sure no line feeds or carriage returns interfere with anything
TotalFile = Replace(Replace(Replace(TotalFile, _
vbCr, ","), vbLf, ","), ",,", ",")
' Write the list to the column starting at StartAt
Arr = Split(TotalFile, ",")
Range(StartAt).Resize(UBound(Arr) + 1) = WorksheetFunction.Transpose(Arr)
Whoops:
Close
End Sub

You would call this subroutine from your own function specifying the
filename along with its full path and the address of the cell to start at.
Something like this...

Sub TestMe()
TextFileToColumn "c:\temp\test.txt", "J8"
End Sub

Note: As written, the macro must be run from the sheet where the specified
cell address is located. This restriction can be removed by providing an
argument in the subroutine to receive the sheet name and then providing a
the necessary Worksheets property call to implement it. I didn't do that
here because my main point was to show the looping assignments were not
needed.

--
Rick (MVP - Excel)


"RB Smissaert" wrote in message
...
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!!