1

I didn't want to hijack a thread, but I am working off of this OP's application: Extract a single line of data from numerous text files and import into Excel

The solution there is amazing and works for his application:

    Sub ExtractGPS()
    Dim filename As String, nextrow As Long, MyFolder As String
    Dim MyFile As String, text As String, textline As String, posGPS As String

    MyFolder = "C:\Users\Desktop\Test\"
    MyFile = Dir(MyFolder & "*.txt")

    Do While MyFile <> ""
        Open (MyFolder & MyFile) For Input As #1
        Do Until EOF(1)
            Line Input #1, textline
            text = text & textline
        Loop

        Close #1
        MyFile = Dir()
        posGPS = InStr(text, "GPS Position")
        nextrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).row + 1
        Sheet1.Cells(nextrow, "A").Value = Mid(text, posGPS + 33, 37)
    Loop
End Sub

So I am also in need to extract text from multiple text files, however with my application I will have different sized string lengths that will not always equal 37 characters in length.

Sheet1.Cells(nextrow, "A").Value = Mid(text, posGPS + 33, 37)

...

    for example:
GPS Position                    : 50 deg 7' 33.40" N, 5 deg 30' 4.06" W
GPS Position                    : 20 deg 7' 22.0" N, 9 deg 2' 3.5" W
GPS Position                    : 50 deg 7' 29.401" N, 5 deg 20' 9.095" W

I am a noob to VBA but know enough to make things happen. To me it seems like there has to be a way to specify until the end of line. Or I need to make a variable that counts the number of characters until if finds a carriage or something in that manner.

Obviously in the OP's original application the the string length wouldn't vary and the above GPS Positions would never print out like I have above. If someone could help me out with my application I would greatly appreciate it.

Mike

mike bannister
  • 13
  • 2
  • What type of line breaks do your files have? – RBarryYoung Sep 24 '21 at 13:47
  • They're just standard text files. I think it's a standard carriage return CR. – mike bannister Sep 24 '21 at 14:02
  • Standard text files can have any of the three line breaks valid for Windows: CR, CR+LF and LF. CR+LF is the most common on Windows, but all three are possible and valid. If the files are produced in a consistent way (ie., by the same program all the time) then they will probably have the same line-break all the time and all you have to do is to check them (I believe that Notepad++ can tell you this, or you can examine it from your own program in debug mode). – RBarryYoung Sep 24 '21 at 14:08

1 Answers1

1

You can use InStr to return the index of the next linebreak and use that, with some math, as the length argument of Mid. Like:

Mid(Text, posGPS + 33, InStr(posGPS, Text, vbCr) - posGPS - 33)

Note: If vbCr isn't working, you can try vbLf as well.

Toddleson
  • 4,321
  • 1
  • 6
  • 26
  • 1
    This will work for both CR and CR+LF, the most common line breaks in Windows. However, line breaks could also be LF. – RBarryYoung Sep 24 '21 at 13:48
  • The vbCr didn't work, and got a runtime error: Invalid procedure call or argument. vcLf totally worked. Thanks so much – mike bannister Sep 24 '21 at 14:04
  • @mikebannister Yeah if the file you're reading doesn't use `vbCr` then `InStr` will return 0 which means the Length argument will be `0 - posGPS - 33` and you aren't allowed to input negative numbers for the Length argument so it would return an error. Try `vbLf` instead of `vbCr` – Toddleson Sep 24 '21 at 14:07
  • Thanks for the breakdown, very useful information for a beginner. – mike bannister Sep 24 '21 at 14:12