ADO Syntax proble
 

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

ADO Syntax proble
 

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)








"Ralf Meuser" wrote:

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

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

ADO Syntax proble
 

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

Best regards
Ralf


"Tim Williams" <timjwilliams at gmail dot com a écrit dans le message de
news: ...
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