Ralph,

Here is your much awaited reply to "can you dumb it down for me please":

strFullPathName and rngAnchor are two parameters for the
SplitTextDataToWorksheet procedure. So, in order to run
SplitTextDataToWorksheet, you need to provide both parameters with the
appropriate arguments. strFullPathName is the full file path name of the
file that you want to read the data from, and rngAnchor is the cell for which
you want the data output, i.e. the data will be placed in the rngAnchor
column but in the first row (given that the data you are reading from is
rather large, placing the data in the first row allows you to take advantage
of the entire worksheet size). For example, you can run the code with a
separate procedure, calling the SplitTextDataToWorksheet procedu

Sub TestSplitText()
SplitTextDataToWorksheet strFullPathName:="C:\Documents and
Settings\Matt\Desktop\Temp\Yahoo\Data\AA.txt", rngAnchor:=Range("A1")
End Sub

So, the argument for SplitTextDataToWorksheets strFullPathName parameter is
"C:\Documents and Settings\Matt\Desktop\Temp\Yahoo\Data\AA.txt" and the
argument for SplitTextDataToWorksheets rngAnchor parameter is Range(€œA1€�).
Youll notice that strFullPathName requires a string argument (hence the
double quotes around the file path name), and rngAnchor requires a range
object. You'll need to change the strFullPathName argument to fit your file
path name (because I highly doubt you have a file called AA.txt in
C:\Documents and Settings\Matt\Desktop\Temp\Yahoo\Data). You can change
Range("A1") to be what you want (i.e. Range("B1"), Range("C1"), etc.). Once
you make the changes, run TextSplitText. Of course, you will need to copy
and paste both TextSplitText and SplitTextDataToWorksheet into a code module.
(You can do this by hitting Alt+F11 to open the Editor, then click Insert
from the menu bar, and then click Module1. Lastly, click View from the menu
bar and then click Code. The cursor should now be blinking in the code
window. Copy and paste the text).

How SplitTextDataToWorksheet works:
See the commented code in the SplitTextDataToWorksheet

Sub SplitTextDataToWorksheet(strFullPathName As String, _
rngAnchor As Range)

'"Dim" is a keyword that dimensions a variable, or in other
' words, it tells the computer "I have a varialbe I want
' to use in the program, make some room for it." The
' "As" means to make it a particular data type, or in other
' words, allocate a certain size of memory for the variable.
' (For example, a Boolean data type takes up less room than
' a Variant data type because Boolean holds two values
' - True and False, but a Variant might be an array that
' holds 10,000 values).
Dim objFSO As Object
Dim objFStream As Object
Dim strLine As String
Dim varSplit As Variant
Dim intColAnchor As Integer

'Create an object that references the FileSystemObject ("FSO"), an
' object that provides access to a computer's file system. This
' allows you to perform the same sort of operations you are
' already used to, e.g. open a file, close a file, copy a file,
' etc.
Set objFSO = CreateObject("Scripting.FileSystemObject")

'One of the the methods of the FSO object is to open a text file.
' You can think of this as if you opened the text file in NotePad.
'Creat an object that references the "open" text file.
Set objFStream = objFSO.OpenTextFile(strFullPathName)

'Store all of the string content in the text file into a string
' variable. Think of this as if you selected the text in
' NotePad and copied it.
strLine = objFStream.ReadAll

'Take the copied text and replace vbCr and vbLf characters with a
' comma. vbCr is a constant code for a carriage return (i.e.
' as if you hit "Enter" on your keyboard). vbLf is a constant
' code for a line feed character (i.e. as if you hit "Alt+Enter"
' on your keyboard. vbCrLf is a carriage return followed by a
' line feed character.
'Typically, text files will contain vbCr and/or vbLf characters.
' These characters may not be visible to your eye when you open
' the file, but they are there. You may need to change the
' constant (i.e. the vbCrLf) to fit your needs if you don't get
' the anticipated results. (For example, you could have
' strLine = Replace(strLine, vbLf, ",") instead).
strLine = Replace(strLine, vbCrLf, ",")

'Use the Split Funciton. If you are familiar with Text to Columns
' in native Excel, then think of the Split function in a similar
' manner. You are taking text and breaking it by a designated
' delimiter, or character (e.g. a space, a tab, a comma, etc).
'Split retuns a zero-based, one-dimensional array containing a
' specified number of substrings. Thus, varSplit will be an
' array of strings "split" by the delimiter. The first argument
' is all of the text from the text file and the delimiter to
' split by is a comma.
varSplit = Split(strLine, ",")

'Since the data from the text file is stored in a variable, close
' the text file because the text file is no longer needed.
objFStream.Close

'Clear the object/memory.
Set objFStream = Nothing

'Clear the object/memory.
Set objFSO = Nothing

'Create a reference to the column number of rngAnchor, i.e. the
' argument which contains the output location.
intColAnchor = rngAnchor.Column

'rngAnchor.Parent refers to the parent of rngAnchor. In a normal
' Excel hierarchy you have Application.Workbook.Worksheet.Range.
' The parent to the range is the worksheet (i.e.
' rngAnchor.Parent), the parent to the worksheet is the workbook
' (i.e. rngAnchor.Parent.Parent), etc.
'A With statement is a way to execute a series of statements on a
' single object. So take the parent of rngAnchor (i.e. the
' worksheet from which rngAnchor is located) and perform the
' statments inside the With End With statment.
'So, the .Cells is interpretted as follows:
' rngAnchor.Parent.Cells. Rather than having to repeat
' rngAnchor.Parent.Cells multiple times in your syntax, the code
' With statement will append it for you.
With rngAnchor.Parent
'Take a look at the TRANSPOSE function in Excel (i.e. search the
' Excel help for Transpose). This funciton is an array
' function, hence why it is being used on varSplit (which was
' created as a one-dimensional array previously).
'UBound is a function that returns the largest available
' placeholder in an array. So, if your text file had 10,000
' entries (separated by a comma) then UBound would be 9,999.
' (Since the array is zero based, the 1st element is in the 0
' index and the 10,000th element is in the 9,999 index). If
' you ran the same procedure on another text file which had 10
' entries (separated by a comma), then UBound would be 9.
'Cells is a one based referencing system that operates off of
' numbers. So Cells(1,1) is the same as Range("A1"),
' Cells(2,2) is the same as Range("B2"), Cells(2,3) is the
' same as Range("C2"), etc.
'Range(.Cells(), .Cells()) creates a Range specified by two cell
' locations, i.e. the first cell in the specified column on
' the first row and the last cell in the specified column on
' the row defined by the UBound in the array + 1 (again, +1 is
' because the array is zero based and as mentioned previously
' 10,000 entries will have a 9,999 UBound and it will take
' 9,999 + 1 to get to 10,000).
'In order to output all of the values in the varSplit array by
' using the TRANSPOSE function, you need to make the size of
' the worksheet range the same as the array.
'The "_" is a line continuation character. It's as if the line
' after "_" were coded on the same line of code for which the
' "_" was coded.
Range(.Cells(1, intColAnchor), _
.Cells(UBound(varSplit) + 1, intColAnchor)) = _
Application.WorksheetFunction.Transpose(varSplit)
End With

End Sub

"Ralph" wrote:

i soooooooo wish i knew what you were talking about Matthew, you are way over
my head with those instructions, can you dumb it down for me please?

"Matthew Herbert" wrote:

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