title: Ignored columns using vb6 to extract from excel
body: <p>I am trying to extract a table of values from an excel (2003) spreadsheet using vb6, the result of which needs to be stored in a (adodb) recordset. The table looks like this:</p>
  
  <pre>
      Name   Option.1  Option.2  Option.3  Option.4  Option.5  Option.6 
      -----------------------------------------------------------------
      Name1         2         3         4
      Name2         2         3         4
      Name3         2         3         4
      Name4         2         3         4
      Name5         2         3         4
      Name6         2         3         4
      Name7         2         3         4
      Name8         2         3         4
      Name9         2         3         4         5         6         7  
  </pre>
  
  <p>Upon connecting and executing the query "<code>SELECT * FROM [Sheet1$]</code>" or even a column-specific, "<code>SELECT [Option#6] FROM [Sheet1$]</code>" (see footnote 1) and looping through the results, I am given <code>Null</code> values for the row <code>Name9</code>, <code>Option.4</code> --&gt; <code>Option.6</code> rather than the correct values 5, 6, and 7. It seems the connection to the spreadsheet is using a "best guess" of deciding what the valid table limits are, and only takes a set number of rows into account.  </p>
  
  <p>To connect to the spreadsheet, I have tried both connection providers <code>Microsoft.Jet.OLEDB.4.0</code> and <code>MSDASQL</code> and get the same problem.</p>
  
  <p>Here are the connection settings I use:</p>
  
  <pre><code>Set cn = New ADODB.Connection
  With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" &amp; filePath &amp; ";Extended Properties=Excel 8.0;"
      - - - - OR - - - - 
      .Provider = "MSDASQL"
      .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" &amp; _
                          "DBQ=" &amp; filePath &amp; ";MaxScanRows=0;"
      .CursorLocation = adUseClient
      .Open
  End With  
  Set rsSelects = New ADODB.Recordset
  Set rsSelects = cn.Execute("SELECT [Option#5] FROM " &amp; "[" &amp; strTbl &amp; "]")
  </code></pre>
  
  <p>This problem only occurs when there are more than 8 rows (excluding the column names), and I have set <code>MaxScanRow=0</code> for the <code>MSDASQL</code> connection, but this has produced the same results.</p>
  
  <p>Notable project references I have included are: </p>
  
  <ul>
  <li>MS ActiveX Data Objects 2.8 Library</li>
  <li>MS ActiveX Data Objects Recordset 2.8 Library</li>
  <li>MS Excel 11.0 Object Library</li>
  <li>MS Data Binding Collection VB 6.0 (SP4)</li>
  </ul>
  
  <p>Any help in this matter would be very appreciated!</p>
  
  <p>(1) For some reason, when including a decimal point in the column name, it is interpreted as a #.</p>
  
  <hr>
  
  <p>Thanks everyone! Halfway through trying to set up a <code>Schema.ini</code> "programmatically" from <a href="http://support.microsoft.com/kb/155512" rel="nofollow noreferrer">KB155512</a> <a href="https://stackoverflow.com/questions/229404/ignored-columns-using-vb6-to-extract-from-excel#229721">onedaywhen</a>'s excellent <a href="http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/" rel="nofollow noreferrer">post</a> pointed me towards the solution:</p>
  
  <pre><code>.Provider = "Microsoft.Jet.OLEDB.4.0"
  .ConnectionString = "Data Source=" &amp; filePath &amp; ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
  </code></pre>
  
  <p>I would encourage anyone with similar problems to read the post and comments, since there are slight variations to a solution from one person to another.</p>