|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
不好意思,刚才那一段乱码了,重新再发一遍。
Sub total()
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 |
|