Extract certain information from text files

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:35 PM GMT


I am using2003. Now I need to extract certain information from text files. My text file is like below:

0.208485 1

0.213333 1

0.218182 0.9

0.22303 0.6

0.227879 0.42

0.232727 0.3

The file contains 2 columns, first I need to check the second column and find to value closest to 0.5 and read the value in the same row in the first column to(in above case, the value is 0.227879). Could you give me some good ideas of this question?

And I do not want to read whole text file tobecause the file is big. So it would be better we can first do the data filtering in text file and then just read the information we want to excel.


I like this question because it exposes a capability of VBA inthat few people know about, so it is rarely taken advantage of.

Here is some code that does what you describe:


Sub FindClosest()

  'Finds value closest to 0.5 in column 2 of Find.txt file and place
  'value from column 1 in cell B4 of Sheet1
  Dim Val1          As Double      'Value from column 1
  Dim Val2          As Double      'Value from column 2
  Dim ClosestVal1   As Double
  Dim ClosestVal2   As Double
  Dim Rec           As String
  Dim Vals          As Variant
  ClosestVal2 = 1000000#
  Open "Find.txt" For Input As #1
  'loop thru all records in file
     Line Input #1, Rec
     Vals = Split(Rec, " ")
     Val1 = Vals(0)
     Val2 = Vals(1)
     If Abs(Val2 - 0.5) < Abs(ClosestVal2 - 0.5) Then
        ClosestVal1 = Val1
        ClosestVal2 = Val2
     End If
  Loop Until EOF(1)
  Worksheets("Sheet1").Range("B4") = ClosestVal1
  MsgBox "Done", vbInformation

End Sub


In this code I assume that the text file is named "Find.txt" and is located in the current working folder. If it is not in the working folder you can use the full path, e.g., C:\My Documents\VBAstuff\Find.txt".

When if finds the second value closest to 0.5 it places the first value in cell B4 of Sheet1.


©2021 eLuminary LLC. All rights reserved.