If you have a text file to import, but only need some of the data, you could just import the whole thing and then manually scrub the data to find what you want. Text files can contain a lot more lines than an Excel worksheet, but much of it may be fluff. Here's a method for importing only the data you need.
First I created a text file with some sample data, here's a screenshot:
I realize it's short, but it serves our purpose. Here is the import sub:
Sub ConditionalImport()
Dim lNum As Long
Dim strLine As String
Dim lNextRow As Long
Application.ScreenUpdating = False
' get number of next free file
lNum = FreeFile
' open text file for input, loop through each line,
' only import rows we need
Open "C:\testfile.txt" For Input As #lNum
Do While Not EOF(lNum)
lNextRow = WorksheetFunction.CountA(Range("A:A"))
Line Input #lNum, strLine
'
' test "strLine" here
'
Loop
Close lNum
Application.ScreenUpdating = True
End Sub
The part right after "Line Input #lNum, strLine" is the part you would customize. Each line of the text file is read into the string variable strLine. Now you can test to see if it is a number (IsNumeric), has a certain number of characters (Len), the way you would do with any other String to see if meets the criteria you want to specify.
For example, if I only wanted to import the data rows that start with "ABC", I would use this:
Sub ConditionalImport()
Dim lNum As Long
Dim strLine As String
Dim lNextRow As Long
Application.ScreenUpdating = False
' get number of next free file
lNum = FreeFile
' open text file for input, loop through each line,
' only import rows we need
Open "C:\testfile.txt" For Input As #lNum
Do While Not EOF(lNum)
lNextRow = WorksheetFunction.CountA(Range("A:A"))
Line Input #lNum, strLine
If (Left$(strLine, 3) = "ABC") Then
Cells(lNextRow + 1, 1).Value = strLine
End If
Loop
Close lNum
Application.ScreenUpdating = True
End Sub
Enjoy,
JP
About JP
5 Comments:
Instead of writing each line to the sheet as it is approved, write it to an array. Then at the end of the procedure, dump the array in one operation to the worksheet.
(Also, your original code increments lNextRow even if the input value for that row is not approved.)
Sub ConditionalImport()
Dim lNum As Long
Dim strLine As String
Dim lNextRow As Long
dim vArray() as Variant
Application.ScreenUpdating = False
' get number of next free file
lNum = FreeFile
' open text file for input, loop through each line,
' only import rows we need
Open "C:\testfile.txt" For Input As #lNum
lNextRow = 0
ReDim vArray(1 To 1, 1 To 1)
Do While Not EOF(lNum)
Line Input #lNum, strLine
If (Left$(strLine, 3) = "ABC") Then
lNextRow = 1 + lNextRow
ReDim Preserve vArray(1 To lNextRow, 1 To 1))
Cells(lNextRow + 1, 1).Value = strLine
End If
Loop
ActiveSheet.Cells(1, 1).Resize(lNextRow).Value = vArray
Close lNum
Application.ScreenUpdating = True
End Sub
I like your code better, but where do you see lNextRow being incremented? It's based on "WorksheetFunction.CountA(Range("A:A"))", which wouldn't change if we didn't write the line to the worksheet.
–JP
JP – lNextRow updates here after the condition is tested and passes:
If (Left$(strLine, 3) = “ABC”) Then
lNextRow = 1 + lNextRow
I've an error on this line
ReDim Preserve vArray(1 To lNextRow, 1 To 1))
What's the error message?