|
Sub 求和()
Set cn1 = CreateObject("adodb.connection")
Set rs1 = CreateObject("adodb.recordset")
Set cn2 = CreateObject("adodb.connection")
Set rs2 = CreateObject("adodb.recordset")
r = Sheet1.Range("a65536").End(xlUp).Row
arr = Sheet1.Range("a2:A" & r)
cg = ThisWorkbook.Path & "\成功明细.xlsx"
gc = ThisWorkbook.Path & "\工程明细.xlsx"
cn1.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & cg
cn2.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & gc
ReDim brr(1 To UBound(arr), 1 To 7)
For i = 1 To UBound(arr)
sql = "select sum(成功金额) from [sheet1$] where 组合='" & arr(i, 1) & "'"
rs1.Open sql, cn1, 1, 3
k = k + 1
brr(k, 1) = rs1.Fields(0)
sql = "select sum(发票含税金额),sum(发票不含税金额) from [sheet1$] where 组合='" & arr(i, 1) & "'"
rs2.Open sql, cn2, 1, 3
brr(k, 6) = rs2.Fields(0)
brr(k, 7) = rs2.Fields(1)
rs1.Close
rs2.Close
Next i
Sheet1.Range("e2").Resize(UBound(arr), 7) = brr
End Sub
你测试一下 |
|