Hi Robert,

Am Tue, 19 May 2015 02:24:23 -0700 schrieb Robert Crandal:

I am looking into the idea of creating a spreadsheet that stores all the
words
of multiple documents, sorted in column A. The spreadsheet will grow bigger
over time, because I will keep adding more and more words each day.

write the new word under the existing words, then sort the column and
remove duplicates. With the following code you only have to change the
path and file name of your txt. file. Or you write a loop over all .txt.
files:

Sub Test4()
'
'Writes all words to column A and then sort ascending
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< <<<<<<

'New 2015-05-09 CB
'Revised 2015-05-10 CB
'Revised 2015-05-19 CB

Dim strFN As String
Dim objReadFile As Object, myDic As Object, objFSO As Object, re As
Object
Dim strText As String, strTemp As String
Dim varText() As Variant, varOut As Variant, varTmp() As Variant
Dim i As Long, LRow As Long, n As Long
Dim ptrn, Match, Matches
Dim FERow As Range

Application.ScreenUpdating = False

'Modify path and file name
strFN = "C:\Data.txt"

Set myDic = CreateObject("Scripting.Dictionary")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set re = CreateObject("vbscript.regexp")

'Opens the text file and read the text into a string
Set objReadFile = objFSO.opentextfile(strFN)
strText = objReadFile.readall
objReadFile.Close

'Separate all "words"
ptrn = "\w+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Matches = re.Execute(strText)
ReDim Preserve varText(Matches.Count - 1)
For Each Match In Matches
varText(n) = LCase(Match.Value)
n = n + 1
Next

Cells(Rows.Count, "A").End(xlUp)(2).Resize(UBound(varText) + 1) = _
Application.Transpose(varText)

With Range("A:A")
.Sort , key1:=Range("A1"), order1:=xlAscending, Header:=xlNo
.RemoveDuplicates Columns:=1, Header:=xlNo
End With

Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional