ADO Recordset to Excel spreadsheet opens properly

Question

My spreadsheet is reading data from another spreadsheet using ADO. The VBA code works properly in MS Excel 2007. However, in MS 2013, opening the recordset generates the error: No value given for one or more required parameters.

The line that errors out reads:

recset.Open sqlCommand, _
    conn, adOpenStatic, adLockReadOnly, adCmdText

I double checked my two variables.

• sqlCommand has the (sanitized) value: SELECT * FROM [Sheet1$] WHERE [F1] IS NOT NULL ORDER BY [F1] ASC.
• conn is the Connection object, with (sanitized) value: Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\user\Documents\path\workbook_to_read.xlsx;Mode=Share Deny None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False;

The other three parameters are ADO constants.

My best guess is that the Provider is for the wrong version, either of Excel or ADO. I don't know what the correct version is, nor how to make the code work in both environments.

Edit and facepalm: Most of the Excel files I'm reading from do not have headers for the first two fields. THIS ONE DOES, and I had to replace [F1] with the correct field name. Please close.

Answer 1

Most of the Excel files I'm reading from do not have headers for the first two fields. THIS ONE DOES, and I had to replace [F1] with the correct field name.

(Answer pasted from my edit on 10 July 2015.)

Answer 2

Hi i was looking for some answer i came up with this Treating data as text

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;  
Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

And when i run some test in Excel it shows this:

Provider=Microsoft.ACE.OLEDB.12.0 ...  
    Extended Properties=""HDR=YES;""; ...
Jet OLEDB:Bypass ChoiceField Validation=False;

So i think that your missing parameter must be the Extended Properties