ADO Syntax proble

R

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
 
P

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)
 
T

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
 
R

Ralf Meuser

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

Best regards
Ralf


Tim Williams said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top