|
试试这段代码。
Sub 汇总()
Dim sql$
Start = Timer
With Sheet1
Application.ScreenUpdating = False
.[c3:h12].ClearContents
Set conn = CreateObject("adodb.connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0; hdr=yes; imex=2'; Data Source=" & ActiveWorkbook.FullName
sql = "transform sum(½ð¶î) select Ã÷ϸ˵Ã÷ from [sheet2$] group by Ã÷ϸ˵Ã÷ pivot ²¿ÃÅ˵Ã÷"
Set rs = CreateObject("adodb.recordset")
rs.Open sql, conn, 1, 3
For Each field In rs.fields
.Cells(2, 2).Offset(0, i) = field.Name
i = i + 1
Next
.Cells(3, 2).CopyFromRecordset rs
conn.Close
Set conn = Nothing
Application.ScreenUpdating = True
End With
MsgBox Timer - Start & "Ãë"
End Sub |
|