i copied and pasted the code into the vb editor and ran the macro and got an
error -

"compile error:expected end sub"

i am kind of clueless about all this, can you please DUMB IT DOWN for me?

thanks!

"RB Smissaert" wrote:

Something like this is probably a lot faster:


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

Sub test()

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

str = TextFileToString("C:\testfile.txt")
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(Cells(1), Cells(UBound(arr2), 1)) = arr2

End Sub


RBS



"Matthew Herbert" wrote in
message ...
Ralph,

This is a procedure I wrote for taking Yahoo! pricing data (which I
download
into a text file) and placing the data into a worksheet. strFullPathName
is
the complete path name of the file (e.g. C:\test.txt) and rngAnchor is the
upper-left cell for the output (e.g. Range("A1")). Change the delimiter
in
the Split function to fit your needs and remove the TextToColumns syntax
if
you don't need it.

Best,

Matthew Herbert

Sub SplitTextDataToWorksheet(strFullPathName As String, rngAnchor As
Range)
Dim objFSO As Object
Dim objFStream As Object
Dim strLine As String
Dim varSplit As Variant
Dim intColAnchor As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFStream = objFSO.OpenTextFile(strFullPathName)
strLine = objFStream.ReadAll
varSplit = Split(strLine, Chr(10))
objFStream.Close
Set objFStream = Nothing
Set objFSO = Nothing
intColAnchor = rngAnchor.Column

With rngAnchor.Parent
Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1,
intColAnchor)) = Application.WorksheetFunction.Transpose(varSplit)
Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1,
intColAnchor)).TextToColumns Comma:=True
End With

End Sub


"Ralph" wrote:

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!!