ADO Syntax proble
The SQL passed to the Open method should be a string. You need to
concatenate the various parts together: right now you're passing (eg) the
literal value "objWb7.Cells(1,2)" and not the value in the cell.
rs.Open "SELECT SUM(CA) as TCA From [KPI4-2$] Where GRP = 10 and " & _
" YEAR= " & objWb7.Cells(1,2).Value & " and MONTH= " &
objWb7.Cells(2,2), _
cnn, 3 ,3,&H0001
Or something like that - not sure about quoting the values...
Tim
"Ralf Meuser" wrote in message
...
Hi there
I'm trying to totalise some figures from a sheet. But I don't know how to
use cells in the where clause.
I tryed the code below, but I get error 80040E14 function objWb7.Cells not
defined.
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\uti\KPI\KPI04A.xls; Extended Properties=""Excel 8.0;HDR=Yes;"";"
rs.Open "SELECT SUM(CA) as TCA From [KPI4-2$] Where GRP = 10 and
YEAR=objWb7.Cells(1,2) and MONTH=objWb7.Cells(2,2)",cnn,3,3,&H0001
Thanks in advance for any help
Best regards
Ralf
|