Sub ado1()
Set cnn = CreateObject("adodb.connection")
cnn.Open "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
Sql = "Select 物料编码,产品名称,规格型号,单位,sum(库存) From (select 物料编码,产品名称,规格型号,单位,库存 from [项目1$] union all select 物料编码,产品名称,规格型号,单位,库存 from [项目2$] union all select 物料编码,产品名称,规格型号,单位,库存 from [项目3$] ) Group By 物料编码,产品名称,规格型号,单位"
Sheets("汇总").Range("B2").CopyFromRecordset cnn.Execute(Sql)
Set cnn = Nothing
rl = Sheets("汇总").Cells(Rows.Count, 2).End(xlUp).Row
For x = 2 To rl
Cells(x, 1) = x - 1
Next
End Sub |