Text document edition and manipulation

[hinubhai]

Hi everyone,
I am trying to write a Macro than can modify a text documents and edit it according to my requirement. For example i have a text document which contains lines:

facet normal -6.930870e-001 -7.208539e-001 -0.000000e+000
outer loop
vertex 3.535534e+000 3.535534e+000 0.000000e+000
vertex 3.394004e+000 3.671613e+000 5.000000e+000
vertex 3.394004e+000 3.671613e+000 0.000000e+000
end loop

I would like to edit this text file select only the numerical values in the loop and make them comma separated. This text would look like
3.535534e+000, 3.535534e+000, 0.000000e+000
3.394004e+000, 3.671613e+000, 5.000000e+000
3.394004e+000, 3.671613e+000, 0.000000e+000

Also what would you do if you have n number of loops? I have read that the following manipulation has to be done using Strings. I shall be grateful if you could provide a Macro to this.

Thanks.

[karedog]

Hi,

This code will change the data to desired format. It will also ask for the range where the macro should be performed, so you don't need to bother about loops.

Sub Test()
  Dim rng As Range, cell As Range, str1 As String, v, i As Long

  On Error Resume Next
    Set rng = Application.InputBox(prompt:="Select the range", Type:=8)
    If Err.Number <> 0 Then
       Err.Clear
       Exit Sub
    End If
  On Error GoTo 0

  Application.ScreenUpdating = False
  For Each cell In rng
      str1 = ""
      v = Split(cell.Value, Space(1))
      For i = LBound(v) To UBound(v)
          If IsNumeric(v(i)) Then str1 = str1 & ", " & v(i)
      Next i
      cell.Value = Mid(str1, 3)
  Next cell
  Application.ScreenUpdating = True
End Sub

Regards

[jindon]

Try this

Sub test()
    Dim fn As String, txt As String, e, x, temp As String
    fn = Application.GetOpenFilename("TextFile,*.txt")
    If fn = "False" Then Exit Sub
    txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
    x = Split(txt, "outer loop" & vbCrLf): txt = ""
    With CreateObject("VBScript.RegExp")
        .Global = True: .IgnoreCase = True
        For Each e In x
            If e Like "*end loop*" Then
                temp = Split(e, vbCrLf & "end loop")(0)
                .Pattern = "(\d)(?= ) "
                temp = .Replace(temp, "$1,")
                .Pattern = "[a-z]+ "
                temp = .Replace(temp, "")
                txt = txt & vbCrLf & temp
            End If
        Next
    End With
    Open Replace(fn, ".txt", "_revised.txt") For Output As #1
        Print #1, Mid$(txt, 3)
    Close #1
End Sub