title: Extract a single line of data from numerous text files and import into Excel
body: <p>I have hundreds of text files in a folder and I need to extract a single line from each one and put the info into excel. The text files contain all the metadata for individual photographs and I need to take out just the GPS coordinates. </p>
  
  <p>I have looked through various other similar threads e.g: <a href="https://stackoverflow.com/questions/26610422/extract-data-from-multiple-text-files-in-a-folder-into-excel-worksheet">extract data from multiple text files in a folder into excel worksheet</a></p>
  
  <p>and:</p>
  
  <p><a href="http://www.mrexcel.com/forum/excel-questions/531515-visual-basic-applications-retrieve-data-text-file.html" rel="nofollow noreferrer">http://www.mrexcel.com/forum/excel-questions/531515-visual-basic-applications-retrieve-data-text-file.html</a> (sorry, not stackoverflow!)</p>
  
  <p>and many others, but can't quite get it to work. I'm close but not quite there. </p>
  
  <p>The data in each of the textfiles is set out like this:</p>
  
  <p>...</p>
  
  <pre><code>---- Composite ----
  Aperture                        : 3.8
  GPS Altitude                    : 37.2 m Above Sea Level
  GPS Date/Time                   : 2014:05:15 10:30:55.7Z
  GPS Latitude                    : 50 deg 7' 33.40" N
  GPS Longitude                   : 5 deg 30' 4.06" W
  GPS Position                    : 50 deg 7' 33.40" N, 5 deg 30' 4.06" W
  Image Size                      : 4608x3456
  </code></pre>
  
  <p>...</p>
  
  <p>I have written the following code:</p>
  
  <pre><code>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 &amp; "*.txt")
  
      Do While MyFile &lt;&gt; ""
          Open (MyFolder &amp; MyFile) For Input As #1
          Do Until EOF(1)
              Line Input #1, textline
              text = text &amp; 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
  </code></pre>
  
  <p>It appears to open each of the text files and look through them but only extracts the GPS coordinates from the first file and repeatedly puts this in excel so I end up with hundreds of rows filled with the same data - the GPS coordinates from the first file in the folder.</p>
  
  <p>If anyone can help me to finish this last bit off it would be greatly appreciated!</p>
  
  <p>Thanks</p>