Solved: Looking for a solution

[jperry]

I have a text file full of data. I am trying to find a way to extract a series of
14 character numbers between exclamation points.

This is what I have
sdfhfghjBwBTdNjBwB!12345678901234!BwBTcNjBwBTdN!jBwB!

I am trying to get just the 12345678901234 out of the file into another text file.
There are 3014 instances of serial numbers like this !12345678901234! in the database file.
Is there a way to pull that data and port it to another file, or am I wishful thinking?

[matthewspatrick]

Give this a shot. Relies on the RegExpFind function found here: http://vbaexpress.com/kb/getarticle.php?kb_id=841




[VBA]
Sub GrabThem()

Dim arr As Variant
Dim fso As Object, ts1 As Object, ts2 As Object
Dim TheLine As String
Dim Counter As Long

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts1 = OpenTextFile("c:\folder\subfolder\sourcefile.txt")
Set ts2 = CreateTextFile("c:\folder\subfolder\resultfile.txt", True)

Do Until ts1.AtEndOfStream
TheLine = ts1.ReadLine
arr = RegExpFind(TheLine, "!\d{14}!")
If IsArray(arr) Then
For Counter = 0 To UBound(arr)
ts2.WriteLine Replace(arr(Counter), "!", "")
Next
End If
Loop

ts1.Close
ts2.Close
Set ts1 = Nothing
Set ts2 = Nothing
Set fso = Nothing

MsgBox "Done"

End Sub
[/VBA]

[jperry]

Hmm...I'm getting a compile error.
Sub or Function not defined...

OpenTextFile

[matthewspatrick]

Sorry, change to:

[VBA]
Set ts1 = fso.OpenTextFile("c:\folder\subfolder\sourcefile.txt")

[/VBA]

[matthewspatrick]

and the next line to:

[VBA]
Set ts2 = fso.CreateTextFile("c:\folder\subfolder\resultfile.txt", True)

[/VBA]

[jperry]

That works great. Thank you!

[matthewspatrick]

Glad to help. Please remember to mark the thread as solved