title: VBA to read in tab delimited file
body: <p>I have some code which reads in a tab delimited file where cell reference B2 matches the reference in the first column in the tab delimited file. This works fine where the text file is small. The below works on a sample file with aa bb and cc as the headers with dummy data.</p>
  
  <pre><code>Option Explicit
  </code></pre>
  
  <p>Sub TestImport()
      Call ImportTextFile(Sheet1.Range("B1"), vbTab, Sheet2.Range("A4"))
  End Sub</p>
  
  <p>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</p>
  
  <pre><code>Dim wks As Worksheet
  
  Set wks = rngTgt.Parent
  
  intTgtColIndex = rngTgt.Column
  lngTgtRow = rngTgt.Row
  
  Open strFileName For Input Access Read As #1
  
  While Not EOF(1)
      Line Input #1, strWholeLine
  
      varTemp = Split(strWholeLine, strSeparator)
  
  If CStr(varTemp(0)) = CStr(Range("B2")) Then
                  wks.Cells(lngTgtRow, intTgtColIndex).Resize(, UBound(varTemp) + 1).Value = varTemp
                  lngTgtRow = lngTgtRow + 1
      End If
  
  
  Wend
  
  Close #1
  Set wks = Nothing
  </code></pre>
  
  <p>End Sub</p>
  
  <p>I am trying to get the below bit of code to work using ADO as this will run much faster on a text file with a couple of million records however I am getting an error on the '.Open str' part of the code (no value given for one or more required parameters).</p>
  
  <p>It looks like it is to do with how I am defining the string- could you review and see if there is something I am missing...?</p>
  
  <pre><code>Sub QueryTextFile()
  t = Timer
  Dim cnn As Object
  Dim str As String
  Set cnn = CreateObject("ADODB.Connection")
  cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
  cnn.ConnectionString = "Data Source=C:\Users\Davids Laptop\Documents\Other Ad Hoc\Test Files\;Extended Properties=""text;HDR=Yes;FMT=Delimited;"""
  cnn.Open
  
  Dim rs As Object
  Set rs = CreateObject("ADODB.Recordset")
  str = "SELECT * FROM [test1.txt] WHERE [aa]=" &amp; Chr(34) &amp; Range("B2") &amp; Chr(34)
  
  With rs
      .ActiveConnection = cnn
      .Open str
      Sheet1.Range("A4").CopyFromRecordset rs
      .Close
  End With
  
  cnn.Close
  MsgBox Timer - t
  </code></pre>
  
  <p>End Sub</p>