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

Question

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

Fionnuala · Accepted Answer · 2010-01-18 14:24:51Z

6

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

answered Jan 18, 2010 at 14:24

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Olle Sjögren · Accepted Answer · 2015-06-22 08:00:22Z

4

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

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

edited Jun 22, 2015 at 8:00

Olle Sjögren

5,3923 gold badges35 silver badges55 bronze badges

answered Jan 18, 2010 at 14:25

bryanjonker

3,4543 gold badges26 silver badges38 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related