ADO Recordset到Excel电子表格在Excel 2007中正确打开，Excel 2013中缺少参数 [英] ADO Recordset to Excel spreadsheet opens properly in Excel 2007, has a missing parameter in Excel 2013

问题描述

我的电子表格正在使用ADO从另一个电子表格读取数据。 VBA代码在MS Excel 2007中正常工作。但是，在MS 2013中，打开记录集会生成错误：没有给出一个或多个必需参数的值。

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.

错误出现的行：

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

我重新检查了我的两个变量。

I double checked my two variables.

• sqlCommand 具有（消毒）值： SELECT * FROM [Sheet1 $] WHERE [F1] IS NOT NULL ORDER BY [F1] ASC 。


• conn 是 Connection 对象，具有（消毒）值： 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 = OLED：引擎类型= 37; Jet OLEDB：数据库锁定模式= 0; Jet OLEDB：全局部分批量操作= 2; Jet OLEDB：全局批量交易= 1; Jet OLEDB：新数据库密码= OLEDB：创建系统数据库= False; Jet OLEDB：加密数据库= False; Jet OLEDB：不要在Compact = False上复制区域设置; Jet OLEDB：紧凑无副本修复= False; Jet OLEDB：SFP = False; Jet OLEDB：支持Complex Data = False; Jet OLEDB：Bypass UserInfo Validation = False; Jet OLEDB：有限DB缓存= False; Jet OLEDB：旁路ChoiceFiel d验证= False;

• 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;

其他三个参数是ADO常量。

The other three parameters are ADO constants.

我最好的猜测是， Provider 是错误的版本，无论是Excel还是ADO。我不知道正确的版本是什么，也不知道如何使代码在两个环境中都可以工作。

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.

编辑和面对：
大多数Excel文件我正在阅读，没有前两个字段的标题。这一个，我不得不用正确的字段名替换 [F1] 。 请关闭。

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.

推荐答案

我正在阅读的大部分Excel文件都没有标题前两个领域。这一个，我不得不用正确的字段名称替换 [F1] 。

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.

（从粘贴到我的修改在2015年7月10日。）

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

这篇关于ADO Recordset到Excel电子表格在Excel 2007中正确打开，Excel 2013中缺少参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！