在 MS-Access VBA 中查询 Excel 工作表(使用 ADODB 记录集)

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/2086234/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:14:24  来源:igfitidea点击:

Query Excel worksheet in MS-Access VBA (using ADODB recordset)

sqlexcelms-accessvba

提问by skerit

I'd like to query an Excel worksheet in VBA andspecify conditions.

我想在 VBA 中查询 Excel 工作表指定条件。

The simple query "SELECT * FROM [PCR$]"works perfectly, but I don't know how to add a WHERE clause.

简单的查询"SELECT * FROM [PCR$]"效果很好,但我不知道如何添加 WHERE 子句。

I tried cmd2.CommandText = "SELECT * FROM [PCR$] WHERE ([B1] IS NOT NULL)"but then it complains about missing parameters.

我试过了,cmd2.CommandText = "SELECT * FROM [PCR$] WHERE ([B1] IS NOT NULL)"但后来它抱怨缺少参数。

This is the complete code:

这是完整的代码:


Dim rs2 As New ADODB.Recordset
Dim cnn2 As New ADODB.Connection
Dim cmd2 As New ADODB.Command
Dim intField As Integer
Dim strFile As String

strFile = fncOpenFile
If strFile = "" Then Exit Sub

With cnn2
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source='" & strFile & "'; " & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
    .Open
End With

Set cmd2.ActiveConnection = cnn2
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM [PCR$]"
rs2.CursorLocation = adUseClient
rs2.CursorType = adOpenDynamic
rs2.LockType = adLockOptimistic

rs2.Open cmd2

采纳答案by Fionnuala

In your connection string you say

在您的连接字符串中,您说

 Excel 8.0;HDR=Yes

Which means that the first row will be treated as the header, no matter what it contains. If you want to use F1, F2 etc, say

这意味着第一行将被视为标题,无论它包含什么。如果您想使用 F1、F2 等,请说

Excel 8.0;HDR=No

回答by bryanjonker

Because you have the HDR=Yesoption, the column name should be the data in the first row.

因为您可以HDR=Yes选择,列名应该是第一行中的数据。

http://support.microsoft.com/kb/316934

http://support.microsoft.com/kb/316934