Extract certain information from text files

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

Question

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.

Answer

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
  Do
     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.

Advertisement

©2021 eLuminary LLC. All rights reserved.