Find a Random String within a String

[DMS500Tech]

(I have serched the help files of VBA endlessly for a solution but it appears that I don't know how to ask my question)

I have the following code that opens a text file searches for certain strings and it works great as long as what I am looking for is in a fixed location but it needs to be expanded to search for a variable that is never in the same location within the string.

sample line 1:
LEN: OKCR 02 0 04 05
sample line 2:
DN 4095557200

What I can't figure out is how to create a search for a third set of characters that moves to different postitions from line to line.

sample line 3:
ACB NOAMA SCWID CND NOAMA CNAMD NOAMA CXR CTALL N STD ACRJ INACT

from sample 3 I need to find each line that contains "CXR" and copy it plus the next 15 characters to column 3 row X.
As I stated "CXR" may be in any position from 1 to 76.
Something I just thought about that is also a problem is that after the "CXR" the remaing data may wrap to the next line (if that is an easy fix then okay but I can live without it).

HERE IS WHAT I HAVE NOW:

Sub Extract_DN_and_LEN_from_a_text_files_WORKS()
'Open a text file and extract data and place it in an Excell spread sheet
'one line at a time

Dim intOutFile As String
intOutFile = FreeFile
Dim RecordNum As Long
Dim LinesFromFile, NextLine As String
RecordNum = 0

Open "C:\TEMP\CENTREX.TXT" For Input As #intOutFile ' Enter path of input file

' code to check if the workbbok is open and if the contents of the workbook are protected
If ActiveWorkbook Is Nothing Then Exit Sub ' no active workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & " is protected.", vbCritical, "cannot overwrite."
Close intOutFile
Exit Sub
End If

Workbooks("RESULTS.xls").Worksheets("Sheet1").Activate

With ThisWorkbook.Worksheets("sheet1")

Do Until EOF(intOutFile)
Line Input #intOutFile, NextLine

If Mid(NextLine, 1, 3) = "LEN" Then
LinesFromFile = Mid(NextLine, 10, 16)
RecordNum = RecordNum + 1 ' using this code here will give LEN's that are used as BRIs
.Cells(RecordNum, 1) = LinesFromFile

ElseIf Mid(NextLine, 1, 3) = "DN " Then
LinesFromFile = Mid(NextLine, 4, 10)
.Cells(RecordNum, 2) = LinesFromFile
' RecordNum = RecordNum + 1 ' using this code here will eliminate LENs used as BRIs
End If

Loop
End With
Close intOutFile
End Sub

 

[PHV]

I have serched the help files of VBA endlessly
Have a look at the InStr function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DMS500Tech]

Thanks,
That is exactly what I needed but I may be back. I am having a little trouble with the logic for the next step.

 

[yogia]

That is exactly what I needed but I may be back. I am having a little trouble with the logic for the next step.

Hi DMS500Tech:

Using InStr function as PHV suggested ...

mid(ActiveCell,instr(ActiveCell,"CXR"),15+len("CXR"))

will extract CXR and then additional 15 characters from the string of the ActiveCell. I used len("CXR") in the code for illustration ... I could easily have used 3 there.

I hope this is some help ... you will naturally use what I have posted to suit your specific needs.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[stevexff]

sample line 3:
ACB NOAMA SCWID CND NOAMA CNAMD NOAMA CXR CTALL N STD ACRJ INACT

Are these data really a set of space-delimited fields? In other words, is 'CXR' always in the same logical field, but just not in the same position in the line because the preceeding fields are different lengths?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[DMS500Tech]

stevexff, This is a line from a text file. These are phone features and can range from none to 5 lines.
I am attempting to search each line of a text file for 3 different items and insert them into Excel.
The first 2 work perfectly and were easy because they are always in the same place of every record.
Thanks to PHV I now know the correct command to use for the 3rd is "InStr" but can't seem to work out the syntax of the statement. (by the way I barely qualify as a hack in VBA)
Anyway, logically to me it should be laid out like below but it keeps complaining about about the last line that I try to assign the results to "LinesFromFile".
Debug is telling me that it is an "invalid procedure call or argument" and honestly I can't figure out why.
After I locate the postion of the "CWT" I want to assign it to the variable "LinesFromFile" then insert it into column 3 of the Excel file.
***************************************************
Do Until EOF(intOutFile)
Line Input #intOutFile, NextLine

If Mid(NextLine, 1, 3) = "LEN" Then
LinesFromFile = Mid(NextLine, 10, 16)
RecordNum = RecordNum + 1
.Cells(RecordNum, 1) = LinesFromFile

ElseIf Mid(NextLine, 1, 3) = "DN " Then
LinesFromFile = Mid(NextLine, 4, 10)
.Cells(RecordNum, 2) = LinesFromFile

ElseIf MyPos = InStr(1, NextLine, SearchChar, 1) Then
LinesFromFile = Mid(LinesFromFile, MyPos, 3)
.Cells(RecordNum, 3) = LinesFromFile
End If

 

[DMS500Tech]

I fixed the oops of variables to:

LinesFromFile = Mid(NextLine, MyPos, 3)

 

[PHV]

Replace this:
ElseIf MyPos = InStr(1, NextLine, SearchChar, 1) Then
LinesFromFile = Mid(LinesFromFile, MyPos, 3)
.Cells(RecordNum, 3) = LinesFromFile
End If
with this:
Else
MyPos = InStr(1, NextLine, "CXR", 1)
If MyPos > 1 Then
LinesFromFile = Mid(NextLine, MyPos, 18)
.Cells(RecordNum, 3) = LinesFromFile
End If
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DMS500Tech]

You guys rock!
I did it just like PHV said with a twist.
I set "MyPos to 0 at the begining of the loop and used another "ElseIf MyPos = 0" so I can go ahead and add a forth search.
Of course the 4th one does not work (no results) yet but at least it does not error out.
Thanks so much for everyones help