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

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

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

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

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'"
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

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

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


Need Your Help

Does using ReadDirectoryChangesW require administrator rights?

c++ security winapi notifications filesystemwatcher

The MSDN says that using ReadDirectoryChangesW implies the calling process having the Backup and Restore privileges.

How to check two conditions in an IF statement in windows phone 7 application using c#?

c# windows-phone-7

I am building an application for windows phone 7 where i have a form and i need to validate two conditions here. The condition is that the textbox shouldnot contain null value and also the value sh...