title: Retrieving data from closed workbook using ADODB connection. Some data is skipped?
body: <p>I am currently writing some code that can access a separate workbook through an ADODB connection.  I have chosen this instead of other methods because of speed. Here is my code below:</p>
  
  <pre><code>    Sub GetWorksheetData(strSourceFile As String, strSQL As String, TargetCell As      range)
      Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer, r As Long
      If TargetCell Is Nothing Then Exit Sub
      Set cn = New ADODB.Connection
      On Error Resume Next
      cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;" &amp; _
      "DBQ=" &amp; strSourceFile &amp; ";"
      ' DriverId=790: Excel 97/2000
      ' DriverId=22: Excel 5/95
      ' DriverId=278: Excel 4
      ' DriverId=534: Excel 3
      On Error GoTo 0
      If cn Is Nothing Then
      MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
      Exit Sub
      End If
  
      ' open a recordset
      Set rs = New ADODB.Recordset
      On Error Resume Next
      rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
      ' rs.Open "SELECT * FROM [SheetName$]", _
      cn, adOpenForwardOnly, adLockReadOnly, adCmdText
      ' rs.Open "SELECT * FROM [SheetName$]", _
      cn, adOpenStatic, adLockOptimistic, adCmdText
      ' rs.Open "SELECT * FROM [SheetName$] WHERE [Field Name] LIKE 'A%'", _
      cn, adOpenStatic, adLockOptimistic, adCmdText
      ' rs.Open "SELECT * FROM [SheetName$] WHERE [Field Name] LIKE 'A%' ORDER BY [Field                 Name]", _
      cn, adOpenStatic, adLockOptimistic, adCmdText
  
      ' optional ways of retrieving a recordset
      ' Set rs = cn.Execute("[A1:Z1000]") ' first worksheet
      ' Set rs = cn.Execute("[DefinedRangeName]") ' any worksheet
  
      On Error GoTo 0
      If rs Is Nothing Then
      MsgBox "Can't open the file!", vbExclamation, ThisWorkbook.Name
      cn.Close
      Set cn = Nothing
      Exit Sub
      End If
  
      'RS2WS rs, TargetCell
      TargetCell.CopyFromRecordset rs ' optional approach for Excel 2000 or later
  
      If rs.State = adStateOpen Then
      rs.Close
      End If
      Set rs = Nothing
      cn.Close
      Set cn = Nothing
      End Sub
  </code></pre>
  
  <p>Now, this code mostly works however when a row contains mixed data types, the query will skip some values.  For example:</p>
  
  <p>Original data:</p>
  
  <pre><code>    3844774 12505604
      3844794 12505604
      4266113 3281271
      4295817 1307HX
  </code></pre>
  
  <p>Returned data:</p>
  
  <pre><code>    3844774 12505604
      3844794 12505604
      4266113 3281271
      4295817 
  </code></pre>
  
  <p>Notice how the last bit of data is skipped.  This is true for multiple entries, but only those that contain letters (making it text).  The original table has everything set to text as well.  Any suggestions so that it doesn't skip these lines?  </p>
  
  <p>Thanks in advance!</p>