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'" .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
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
Because you have the HDR=Yes option, the column name should be the data in the first row.