Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PRoble with subtraction | Excel Programming | |||
Heres the proble... | Excel Programming | |||
Heres the proble... | Excel Programming | |||
Heres the proble... | Excel Programming | |||
Heres the proble... | Excel Programming |