PDA

View Full Version : pick up same artno but differnet attribute



helai
06-13-2006, 04:24 AM
I have two sheets placed on different workbooks
Now I want to pick up the items with same artno but different attribute,

if the data are stored in access, I think it's easy
with select...from ...where... rst.open

but now how can I use the same method in excel,because I think it's more quicker thank the normal method
just like
for i=1 to 7777
if worksheets("sheet1").cells(i,1)=...
next i

here attached the code only allowed to open a workbook at the same time
how to create the linkage between these two workbooks?
of course if I copy contents from the other workbooks to the one which I want to use ADO connection,but I think thus I have to preserve one blank sheet and it will cost resources also

Dim CNN As New ADODB.Connection
Dim RST As New ADODB.Recordset
With CNN
.ConnectionString = "provider=microsoft.jet.oledb.4.0;" & _
"extended properties='Excel 8.0;hdr=no';" & _
"data source=C:\book10.xls"


Any advices are welcome
heali

helai
06-14-2006, 06:56 AM
Now I have uploaded these two sheets to a access file,and using ado connection well,because they don't including so much data,
but in this access file,I have another table <b>with over 30000 rows,when I want to use query by through ado connection,
just like
from a, b
where a.art.no=b.art.no and a.attribute<>b.attribute and b.attribute=111
rst. open cnn
It cost me very long time
but actually in table b if you pre-query especially for attribute of 111,then the contens may be no more than 300 row.but in access ,this resualt only showed in it's queries ,and not acted as a normal table,wherether is possible for me create aquery with a and query from b?
how do i use this narrowed content in excel?

how to speed up query when one of sheet is very larger

thanks,
helai