I would certainly try Kenneth's first. Just as I was too obstinate to give up on a RegExp... (Oy vey! I really need to break down and buy a book!)
Option Explicit
Sub exa()
Dim REX As Object ' RegExp
Dim FSO As Object ' FileSystemObject
Dim fsoStream As Object ' TextStream
Dim sLineText As String
Dim i As Long
Dim x As Long
Dim y As Long
Dim aryTemp(1 To 1, 1 To 40000) As String '<oversize last dimension
Dim aryOutput As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(ThisWorkbook.Path & "\test.txt") Then
Set fsoStream = FSO.OpenTextFile(ThisWorkbook.Path & "\test.txt", 1, False, -2)
Else
Exit Sub
End If
Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = False
.IgnoreCase = True
.MultiLine = False
.Pattern = "^(\s*\btest\b\s+)(\S+)"
End With
With fsoStream
Do While Not .AtEndOfStream
sLineText = .ReadLine
If REX.Test(sLineText) Then
i = i + 1
aryTemp(1, i) = REX.Execute(sLineText)(0).SubMatches(1)
End If
Loop
.Close
End With
ReDim aryOutput(1 To i, 1 To 1)
For x = 1 To i
aryOutput(x, 1) = aryTemp(1, x)
Next
Range("A1").Resize(i).Value = aryOutput
End Sub
Hi Ken :-)
Mark