I was recently asked if the routine in the Text-in2 spreadsheet to import and split data from text files could be modified to only import rows that met specified criteria. A similar end result can be achieved by importing the whole file, then using the Excel data filtering routines, but applying the selection criteria at the text import stage does have some advantages:
- It allows selection of data from very large files, without exceeding the row limit in Excel.
- The process is quicker to set up.
- The use of VBA allows the possibility of more flexible import criteria.
This feature has now been added to the spreadsheet, which can be downloaded from the link above. The routine as written checks that the values in the specified columns are within a specified numerical range, but it could easily be amended to work on text.
The download file includes full open source code, and the new routines to read and check the criteria are shown below:
Modifications to the GetData Function:
... ApplyCriteria = Range("ApplyCriteria").Value2 If ApplyCriteria(1, 1) > 0 Then Criteria = Range("CriteriaRng").Value2 NumCrit = UBound(Criteria) StartRow = ApplyCriteria(1, 1) If ApplyCriteria(1, 2) > 0 Then EndRow = ApplyCriteria(1, 2) Else NumCrit = 0 End If ... If NumCrit > 0 Then If i >= StartRow And i <= EndRow Then CheckCriteria DataA2, Criteria, NumCrit, i, m End If
Sub CheckCriteria(ByRef DataA2 As Variant, ByRef Criteria As Variant, ByVal NumCrit As Long, ByVal i As Long, ByRef m As Long) Dim n As Long, Chkcol As Long, DatVal As Double For n = 1 To NumCrit Chkcol = Criteria(n, 1) If Chkcol <> 0 Then DatVal = Val(DataA2(m, Chkcol)) If Criteria(n, 2) <> "" Then If DatVal < Criteria(n, 2) Then m = m - 1 Exit Sub End If End If If Criteria(n, 3) <> "" Then If DatVal > Criteria(n, 3) Then m = m - 1 Exit Sub End If End If End If Next n End Sub
The spreadsheet needs two additional named ranges:
- ApplyCriteria (B16:C16): the row number of the first and last line to be imported
- CriteriaRng (A19:C24): Column number, minimum and maximum values to be imported.
Input and typical output are shown in the screenshot below (click for full size view)
This is nice. Now when you write this, is this just for this site, for this demo, or was there another use for it? Do you put these out there for Do you put these out there for use elsewhere? This took several hours, right?
Most of what I post is things I have done for my own work, education or entertainment. This has the dual benefit of forcing me to complete things to a stage where they are reasonably presentable, and to document and store them in a place where they will get indexed, so I can find them in the future. In this case it was in response to an e-mailed request for help. It was something that I could see being useful to me in the future, so I’m happy to spend a few hours on that sort of thing (if I have a few hours to spare).
Pingback: Daily Download 32: Text functions | Newton Excel Bach, not (just) an Excel Blog
Question received by e-mail:
“I am new to VBA was trying to write code similar to your which I found following a google search, I have tried running your code but the debugger gives a run time 1004 as I am not specifing the range in DestRange = Range(“destrange”).Value2
Can you please explain with an example how in Text In the destrange is specified.”
The easiest way to use the code in Text-in2.xls is to modify the original workbook, but if you want to start from scratch with a new file you will need to create the range names used in the code. In the case of “destrange” you will need a single cell with the name destrange, and another range (where you want the ouput to go) with the name entered in destrange (TextFileOut was used in my example, but it could be any valid name).
Thanks for the prompt reply….I’ll see what happens after work, did not realize you were using names.
Hello Genius.. really impressed with ur work. One small doubt.. Im a nowise in VBA.. could you please tell me wat to change If I have to just apply a direct criteria..? More Like anything that matches the word “Academy”
The criteria have to be numerical at the moment, but it would be possible to amend it to search for text, either in specified columns, or any column. I’ll try and find some time to do that (or if anyone else can recommend a free download that does it already, please let us know).
Pingback: Importing Text Files; Unix Format | Newton Excel Bach, not (just) an Excel Blog