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

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

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" <(E-Mail Removed)> wrote in message
news:47d644f9$0$25577$(E-Mail Removed)...
> 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
>

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: udd2Rb$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:47d644f9$0$25577$(E-Mail Removed)...
>> 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
>>
>
>