EXcel多表之间操作(VBA)

"from [产品资料$] a,[进货$] b,[销售$] c where a.产品代码=b.产品代码 and a.产品代码=c.产品代码"
'[a65536].End(xlUp).Offset(1, 0).CopyFromRecordset conn.Execute(Sql)

 

Sub 汇总()
Range("A2:J100").ClearContents
Set conn = CreateObject("adodb.connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
Sql = "select A.产品代码,A.名称,sum(B.进货数量),B.进货单价,sum(B.进货金额),sum(C.销售数量),C.销售单价,sum(C.销售金额),sum(C.销售数量)*(C.销售单价-B.进货单价),sum(B.进货数量)-sum(C.销售数量) from [产品资料$] as A,[进货$] as B,[销售$] as C where A.产品代码=B.产品代码 and B.产品代码=C.产品代码 group by A.产品代码,A.名称,B.进货单价,C.销售单价"
Sheet2.[a2].CopyFromRecordset conn.Execute(Sql)
conn.Close
Set conn = Nothing
End Sub