You might want to experiment with is code:

Sub AA()
Dim Cnn As String, Sql As String
Cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Application.ActiveWorkbook.FullName & _
";Extended Properties='Excel 8.0;HDR=NO'"
Sql = "SELECT 'Apple' FROM [SHEET1$A1:B6] WHERE F2= 'Tom & Co'"
Set ADORS = CreateObject("ADODB.Recordset")
ADORS.Open Sql, Cnn
' In the Immediate window, type ADORS.GetString to see what has been
extracted.
' Note: The FROM clause specifies 2 columns
' HDR is set to NO, so field names are autonamed F1, F2 etc
' This picks up an arbitrary string Apple
End Sub


"augustus" wrote:

Hi,

This is my code:

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
strConn = strConn & "Data Source=" & Application.ActiveWorkbook.FullName & ";"
strConn = strConn & "Extended Properties='Excel 8.0;HDR=NO'"

strSQL = "SELECT 'Apple' FROM [SHEET1$A1:A4] WHERE [SHEET1$B1:B4] = 'TOM &
Co'"
With Conn
.CursorLocation = adUseClient 'default assumed not client
.ConnectionString = strConn
.Open
End With

Rs.Open strSQL, Conn 'Stopped here
........

I get :
No value given for one or more required parameters

I read somewhere that I must use HeaderRow (HDR=YES) inorder to use other
SQL function, or else, I only get to use select. Is there way to get around
this?

I use A1:B4 (much larger in the real sheet) as a temp dump area, therefore,
I really don't want to use hearderrow, since other record also go here.

Thanks