ADO Syntax proble

[Ralf Meuser]

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

 

[Patrick Molloy]

you don't tell us how you define

objWb7.Cells(2,2)

but Cells is workSHEET method and teh object looks like a workbook. Create
a worksheet object and set it to that workbook's activesheet

DIM objWS as Object
SET objWS = objWb7.ActiveSheet

then change your WHERE clause from this
objWb7.Cells(2,2)
to this
objWS.Cells(2,2)

 

[Tim Williams]

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]

Thanks both of you for your help.
I tryed the second one and now it's working

Best regards
Ralf

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

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