Since it's tab delimited, I would use VBA to open it in Excel instead of reading it as a text file. Without having the file I can't do a test for you or confirm performance improvements but it would look something like this:
Option Explicit
Sub TestImport()
Call ImportTextFile(Sheet1.Range("B1"), vbTab, Sheet2.Range("A4"))
End Sub
Public Sub ImportTextFile(strFileName As String, strSeparator As String, rngTgt As Range)
Dim lngTgtRow As Long
Dim lngTgtCol As Long
Dim varTemp As Variant
Dim strWholeLine As String
Dim intPos As Integer
Dim intNextPos As Integer
Dim intTgtColIndex As Integer
Dim LastRow As Long
Dim StrFile As Workbook
Dim R As Long ' row number of text file
Dim wks As Worksheet
Set wks = rngTgt.Parent
intTgtColIndex = rngTgt.Column
lngTgtRow = rngTgt.Row
Set StrFile = Workbooks.Open(Filename:=strFileName)
With StrFile.Worksheets(1)
' Assumes that there is data in column A of every row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For R = 1 To LastRow
If .Cells(LastRow, 1) = Range("B2") Then
If IsDate(.Cells(R, 2)) = Range("B2") Then
If CDate(.Cells(R, 2)) >= #1/1/2015# And CDate(.Cells(R, 2)) <= #1/1/2017# Then
.Rows(R).EntireRow.Copy wks.Cells(lngTgtRow, intTgtColIndex)
lngTgtRow = lngTgtRow + 1
End If
End If
End If
Next R
StrFile.Close savechanges:=False
Set wks = Nothing
End With
End Sub
Bookmarks