在 MS-Access VBA 中查询 Excel 工作表(使用 ADODB 记录集) [英] Query Excel worksheet in MS-Access VBA (using ADODB recordset)

问题描述

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

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

简单查询"SELECT * FROM [PCR$]" 完美运行，但我不知道如何添加 WHERE 子句.

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

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

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

这是完整的代码:

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

推荐答案

在你的连接字符串中你说

In your connection string you say

 Excel 8.0;HDR=Yes

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

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

Excel 8.0;HDR=No

这篇关于在 MS-Access VBA 中查询 Excel 工作表(使用 ADODB 记录集)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！