Try:

Sub aa()
Cnn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\relational.xls;Extended
Properties=Excel 8.0;"
Sql="'SELECT a.Code,a.Qty,b.Rate, (a.Qty * b.Rate) as Cost FROM [Sheet1$] a,
[Sheet2$] b WHERE a.Code = b.Code;"
Set ADORS = CreateObject("ADODB.RecordSet")
ADORS.Open Sql, Cnn
' .. lookup CopyFromRecordSet in the help file if you want to add results to
another Sheet
End Sub

NOTE: Replace c:\relational.xls by your workbook; you can use
activeworkbook.fullname (you will have to concatenate the cnn string
accordingly)

"R-M" wrote:

Hi

I'm working with Ms Office 2000 and I have problem in MsExcel,
I have my information in 2 sheets like below:

sheet1: sheet2:
_______________ __________________

code rate code qty
_____ ______ ______ __________


My desired result is to have multiplied (rate*qty) for each code in 3rd
sheet of my file.
how is it possible? in other words, can I use some concepts of select
statement like in SQL 2000 enviroment?
or is there another solution?

Any help would be thanked.