在 VBA 中打开与 Excel 电子表格的 ADO 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1091285/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Opening ADO connection to Excel Spreadsheet in VBA
提问by jwoolard
How would I go about opening an ADO connection to an Excel 2007 spreadsheet?
我将如何打开与 Excel 2007 电子表格的 ADO 连接？
I am doing this in order to import the data into Access 2007. Rather annoyingly, the data needs to be fltered and pre-processed before being imported, hence why I want to open an ADO connection to read it.
我这样做是为了将数据导入 Access 2007。相当烦人的是，数据在导入之前需要过滤和预处理，因此我想打开 ADO 连接来读取它。
回答by Helen
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyExcel2007File.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
oConn.Open
Note that you need to use the ACE driverinstead of JET. See also Connection strings for Excel 2007.
请注意，您需要使用ACE 驱动程序而不是 JET。另请参阅Excel 2007 的连接字符串。
回答by Andrew Scagnelli
If you're going to be running the import more than once (i.e.: some type of daily reporting service), you might want to try a different approach than ADO.
如果您要多次运行导入（即：某种类型的每日报告服务），您可能想尝试与 ADO 不同的方法。
I ended up creating a module in Access that pre-processes Excel sheets (since the sheet that gets imported every day changes) and then sets the sheet as the source of a linked table. Then, I query the linked table with a "INSERT INTO" DoCmd.RunSQLcall to get the data out of Excel and into the DB.
我最终在 Access 中创建了一个模块，用于预处理 Excel 工作表（因为每天导入的工作表都会发生变化），然后将工作表设置为链接表的源。然后，我使用“INSERT INTO”DoCmd.RunSQL调用查询链接表，以将数据从 Excel 中取出并放入数据库中。
If you'd like, I can go more into specifics.
如果你愿意，我可以更详细地介绍。
回答by Bala Kumar
Set cnn = New ADODB.Connection
'cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFullFileName & ";Extended Properties = Excel 12.0 Macro; HDR=No;"
'cnn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; data source=" & sFullFileName & ";Extended Properties=Excel 8.0"
cnn.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & sFullFileName
cnn.Open
