在MS-Access VBA中查询Excel工作表(使用ADODB记录集) [英] Query Excel worksheet in MS-Access VBA (using ADODB recordset)
本文介绍了在MS-Access VBA中查询Excel工作表(使用ADODB记录集)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在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屋!
查看全文