|
Sub limonet()
Application.ScreenUpdating = False
Dim Arr As Variant, i%, Cn As Object, StrSQL$
Arr = Range("B3:B9")
Workbooks.Open (ThisWorkbook.Path & "\工作簿1.xlsx")
For i = UBound(Arr) To 1 Step -1
Arr(i, 1) = Cells.Find(Arr(i, 1)).Row + 1
If i = UBound(Arr) Then
StrSQL = "Select Sum([审定(元)]) From [Sheet1$J" & Arr(i, 1) & ":J" & Arr(i, 1) + 1000 & "] Union All " & StrSQL
Else
StrSQL = "Select Sum([审定(元)]) From [Sheet1$J" & Arr(i, 1) & ":J" & Arr(i + 1, 1) - 2 & "] Union All " & StrSQL
End If
Next i
ActiveWorkbook.Close flase: Application.ScreenUpdating = True
Set Cn = CreateObject("Adodb.Connection")
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\工作簿1.xlsx"
Range("C3").CopyFromRecordset Cn.Execute(Left(StrSQL, Len(StrSQL) - 11))
End Sub |
|