title: Excel ADO force data type
body: <p>I have data stored in excel files and I'm trying to run SQL queries on them using VBA.
  Problem is that the data I'm working with is pretty dirty and unfortunately my control over its contents is very limited. Because of that excel is freaking out and is constantly assigning wrong data type to columns.</p>
  
  <p>My best guess was that the type is determined by value inside first row o the table. But unfortunately the behavior is really unpredictable and it is not always the case. Here is the example:</p>
  
  <p><a href="https://i.stack.imgur.com/iaSAn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iaSAn.png" alt="enter image description here"></a></p>
  
  <p>I have columns with strings and empty values, zeros or errors. This columns are treated as double instead of text. And because of that every non number value in this column is erased after executing <code>CopyFromRecordset</code>. 
  I also have columns with numbers and occasional empty values, and then the column is treated as text.</p>
  
  <p>It is messing with SQL queries with <code>WHERE</code> or <code>JOIN</code> clauses. Because if I'm making string comparison on column with a type double it won't work. Same if it happens in reverse if I'm trying to make number comparison on string column.</p>
  
  <p>Sometimes problem can be avoided by setting proper formatting on problematic columns, sometimes by writing something else in the first row. But as I said it is really unpredictable and sometimes it does not work.</p>
  
  <p>Tried something like this (following <a href="https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/datatypeenum?view=sql-server-ver15" rel="nofollow noreferrer">this type table</a>):</p>
  
  <pre class="lang-vb prettyprint-override"><code>dbRecordset.Fields(2).Type = 200
  </code></pre>
  
  <p>But I get <code>Operation is not allowed when the object is open</code>.</p>
  
  <p>I also tried to make manual conversion inside SQL query, but I feel like it much slower and the syntax is really messy. For example here is asserting type double (and it is only one column, I have couple dozen more):</p>
  
  <pre><code>Cdbl(IIf(IsNull(c.[Column4]), 0, c.[Column4])) &gt; 0
  </code></pre>
  
  <p>So - is there a way to tell excel what type of data is inside each column? OR how to avoid my problems?</p>
  
  <p>Here is my code:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Option Explicit
  
  Sub RunCopy()
      Dim dbConnection  As Object
      Dim dbRecordset   As Object
      Dim strSQL        As String
      Dim dbField       As Variant
      Dim fieldCounter  As Long
  
      Dim src_wks As Worksheet
      Dim dst_wks As Worksheet
  
      Set src_wks = Worksheets("Src")
      Set dst_wks = Worksheets("Dst")
  
      Set dbConnection = CreateObject("ADODB.Connection")
      Set dbRecordset = CreateObject("ADODB.Recordset")
  
      ' CONNECTION WITH EXCEL ODBC DRIVER
      dbConnection.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
                            &amp; "DBQ=" &amp; ThisWorkbook.FullName &amp; ";"
  
      ' OPEN RECORDSET
      dst_wks.UsedRange.Clear
      dbRecordset.Open "SELECT d.* FROM [Src$] d WHERE d.[Column4] &gt; 0", dbConnection
      dbRecordset.Fields(2).Type = 200
      With dst_wks
          ' HEADERS
          fieldCounter = 0
          For Each dbField In dbRecordset.Fields
              fieldCounter = fieldCounter + 1
              .Cells(1, fieldCounter).Value = dbField.name
          Next dbField
          ' DATA ROWS
          .Range("A2").CopyFromRecordset dbRecordset
      End With
      dbRecordset.Close
  
      dbConnection.Close
      Set dbRecordset = Nothing: Set dbConnection = Nothing
  End Sub
  </code></pre>