CODEWITHSUNDEEP
excelvba
Experimenter
Experimenter

Reputation: 167

Extract certain lines after string found from file

I want to extract lines 6th, 9th and 12th after string is found in certain line in file. Here search string is output name="BoundingBox">. There might be multiple instances in same file, so I want to repeat the same.

I've below code which extracts the all lines after string is found.

File data:

  ...
  <output name="BoundingBox">
   <struct type="BoundingBox">
  <output name="BottomLeftCorner">
   <struct type="BottomLeftCorner">
  <elem name="X">
    <object type="double" value = "351.8340105482794">
  </elem>
  <elem name="Y">
    <object type="double" value = "319.5377197265625">
  </elem>
  <elem name="Z">
    <object type="double" value = "0">
  </elem>
   </struct>
  </output>
  <output name="TopRightCorner">
   <struct type="TopRightCorner">

Code:

Sub test()
Dim fn As String, txt As String, mySearch As String, x, y
fn = "D:\workdir\Autotest--new.xml"
txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
'mySearch = InputBox("Enter search string")
x = InStr(1, txt, "output name=""BoundingBox"">", 1)
If x = 0 Then
    MsgBox "No match"
    Exit Sub
Else
    txt = Mid$(txt, x)
    y = Split(txt, vbCrLf)
    Cells(1, 1).Resize(UBound(y) + 1).Value = Application.Transpose(y)
End If
End Sub

Upvotes: 0

Views: 91

Answers (1)

Nirostar
Nirostar

Reputation: 199

I would use Open fn For Input As #1 and iterate over the lines with Line Input #1, textline then check if the String is present and count the following lines. Save whatever is needed. Don't forget to close the file with Close #1

Sub test()
    Dim fn As String, txt As String, mySearch As String, x, y
    fn = "D:\workdir\Autotest--new.xml"
    Open fn For Input As #1
    Do Until EOF(1)
        Line Input #1, textline
        If InStr(1, textline, "output name=""BoundingBox"">", 1) > 0 Then
            For i = 1 To 12
                Line Input #1, textline
                Select Case i
                    Case 5
                        Cells(1, 1) = extract_value(textline)
                    Case 8
                        Cells(1, 2) = extract_value(textline)
                    Case 11
                        Cells(1, 3) = extract_value(textline)
                    Case Else
                        'Ignore
                End Select
            Next
        End If
    Loop
    Close #1
End Sub

Where the following function extracts the double value from the textline.

Function extract_value(ByVal textline As String) As Double
    startIndex = InStr(1, textline, "value = """) + 9
    endIndex = InStr(startIndex, textline, """")
    strVal = Mid(textline, startIndex, endIndex - startIndex)
    'strVal = Replace(strVal, ".", ",") 'use this if you have "," as your decimal separator
    extract_value = CDbl(strVal)
End Function

This code is untested because I don't have a full file of your instances.

Further I would recommend to have a look at the XML Parser if you are dealing with XML files: http://dailydoseofexcel.com/archives/2009/06/16/reading-xml-files-in-vba/

Upvotes: 1

Related Questions