|
Sub test1()
ActiveSheet.UsedRange.Offset(1).ClearContents
Application.ScreenUpdating = False
Dim Conn As Object
Dim strConn As String, SQL As String, s As String, p As String, f As String
Set Conn = CreateObject("ADODB.Connection")
s = "Excel 12.0;HDR=YES;Database="
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source="
Conn.Open strConn & ThisWorkbook.FullName
p = ThisWorkbook.Path & "\"
f = Dir(p & "*.xls*")
While f <> ""
If ThisWorkbook.FullName <> p & f Then
SQL = "SELECT 账期,类型,SUM(暂估数),SUM(实际数),SUM(金额) FROM [" & s & p & f & "].[Sheet1$] GROUP BY 账期,类型"
Range("A" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset Conn.Execute(SQL)
SQL = "SELECT 账期,品类,SUM(暂估数),SUM(实际数),SUM(金额) FROM [" & s & p & f & "].[Sheet1$] GROUP BY 账期,品类"
Range("G" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset Conn.Execute(SQL)
SQL = "SELECT 账期,渠道,SUM(暂估数),SUM(实际数),SUM(金额) FROM [" & s & p & f & "].[Sheet1$] GROUP BY 账期,渠道"
Range("M" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset Conn.Execute(SQL)
SQL = "SELECT 账期,地区,SUM(暂估数),SUM(实际数),SUM(金额) FROM [" & s & p & f & "].[Sheet1$] GROUP BY 账期,地区"
Range("S" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset Conn.Execute(SQL)
End If
f = Dir
Wend
Conn.Close
Set Conn = Nothing
Application.ScreenUpdating = True
Beep
End Sub |
评分
-
1
查看全部评分
-
|