It probably will need a bit further cleaning up like this:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


That might be enough and it will work.


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from Table1,
Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel. Did I
say the .txt file is huge? Manually this would take at least one day worth
of work :)

Thanks in advance
Michael