Reading a text file and apply using regex on the resultant string

I would to read the content of text file once and apply regex on the resultant string. I have not been able to figure out how to read text file once.

There is an alternative I have been experimenting with but it not working correctly

Set oFS = oFSO.OpenTextFile(sFilename)
num = 15
mynum = 1
Do Until oFS.AtEndOfStream
    'Cells(num, mynum).Value = o开发者_JAVA技巧FS.ReadLine
     MyString = oFS.ReadLine
     If Not oFS.AtEndOfStream Then
            oFS.SkipLine

     strStrings = Split(MyString, " ")
     For intInd = LBound(strStrings) To UBound(strStrings)

         Cells(num, mynum).Value = strStrings(intInd)

         mynum = mynum + 1
         If mynum Mod 4 = 0 Then
             num = num + 1
             mynum = 1
         End If
    Next

    End If
Loop
oFS.Close
Set oFSO = Nothing

I noticed that the problem is from two sources ...One my function fails woefully once the string to be split is of variable length. How could I patch this up?. Secondly, my file content some non characters which I am suspecting may be causing the issue too. Myfile has varying space after the third line. I am able to get the first two lines

DNI ROLL TEST ON 896_271209

Directional Data Test Started

=> KS 32223.63 Flammas

=> SIP -7.25 Deg

=> RIGHT 90.57 Deg

=> AZIMUTH 105.46 Deg

=> LEFT 73.92 Deg

=> OFFSET -1.15 Deg

Here is a very useful code sniplet I've used before that will store an entire text file in a string that you can then work on (like using regex).

Dim strFile As String
Dim intFile As Long

intFile = FreeFile

Open "C:\File.txt" For Input As #intFile
   strFile = Input$(LOF(intFile), #intFile)  '  LOF returns Length of File
Close #intFile

'Do what you want with strFile

UPDATE: Actually, here's a method I find more safe. I have even shown how to get the file name using the open dialogue box, which is very convenient:

Sub test()

Dim fileString As String
Dim fileName As String

' You can use GetOpenFilename() if you like
fileName = Application.GetOpenFilename("Text Files (*.txt,*.txt")
fileString = Space(FileLen(fileName))

Open fileName For Binary As #1
    Get #1, , fileString
Close #1

' Do what you wish with fileString
MsgBox Len(fileString)
End Sub

If you change this line:

Cells(num, mynum).Value = strStrings(intInd)

to:

Cells(num, mynum).Value = "'" & strStrings(intInd)

it will force Excel to see each line as text. I don't think it likes the equals sign in "=>", because an equal sign is generally used to denote a formula in Excel.

Here's the output I get when I run it with that change:

This doesn't seem very useful to me, but I'm not sure if this gives the output you are looking for-- you didn't supply a lot of detail on what the your output needs to look like. This should help to get rid of your "Application-defined or Object-defined error", though.

If you're looking to run a RegEx on the text, please explain what you are looking for your program to do, and I'm sure one of us could help you write up a RegEx expression.

I later got the above code working ..

Set oFS = oFSO.OpenTextFile(sFilename)
num = 15
mynum = 1
Do Until oFS.AtEndOfStream
    'Cells(num, mynum).Value = oFS.ReadLine
     MyString = oFS.ReadLine

     Do While InStr(MyString, "  ")
     MyString = Replace(MyString, "  ", " ")
    Loop


     strStrings = Split(MyString, " ")
     If UBound(strStrings) > 0 Then
     For intInd = LBound(strStrings) To UBound(strStrings)
         If Not strStrings(intInd) = "=>" Then
         Cells(num, mynum).Value = strStrings(intInd)

         mynum = mynum + 1
         If mynum Mod 5 = 0 Then
             num = num + 1
             mynum = 1
         End If
         End If
    Next
    End If