Find multiple occurrences of string in a text file using VBA-CodePudding

I have a large text file which is contains the muliple text "Process ID #" followed by data such as:

Status   Pending    
 
Process ID #483300 


#1522551  New          

        
Status   Pending    
 
Process ID #483319 


#1522502  New          

        
Status   Pending    
 
Process ID #483313

I have tried using a code to allow me to specify the text file and then used the InStr function but it will only identify the first occurrence. Any help appreciated.

My Final output is like

Process ID #483300 Process ID #483319 Process ID #483313

CodePudding user response：

Please, try using the next function (using RegEx):

Function ExtractProcID(x As String) As Variant
  Dim matches As Object, mch As Object, arr, k As Long
  ReDim arr(Int(Len(x) / 12))
     With CreateObject("VbScript.regexp")
        .Pattern = "Process ID #" & "[0-9]{6,7}"
        .Global = True
        If .test(x) Then
            Set matches = .Execute(x)
            For Each mch In matches
                arr(k) = mch: k = k   1
            Next
        End If
     End With
     If k > 0 Then
        ReDim Preserve arr(k - 1)
        ExtractProcID = arr
    Else
       ExtractProcID = Array("")
    End If
End Function

You can test it on your above string as:

Sub testExtractProcID()
  Dim x As String, arr
  x = "Process ID #483300 " & vbCrLf & _
       "#1522551  New" & vbCrLf & _
      "status Pending" & vbCrLf & _
      "Process ID #483319 " & vbCrLf & _
      "#1522502  New          " & vbCrLf & _
      "status Pending" & vbCrLf & _
      "Process ID #483313 "
      
   arr = ExtractProcID(x)
   Debug.Print Join(arr, ", ")
End Sub