统计销售数量并按顺序排列 | VBA实例教程

Sub search()
Dim cnn As Object, sql$, rs As Object, i
Set cnn = CreateObject("adodb.connection")
cnn.Open "Provider=Microsoft.jet.Oledb.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
sql = "select 货号,品名,sum(数量) as 数量汇总 from [明细表$B1:E765] group by 货号,品名,数量 order by sum(数量) desc"
Sheet12.Range("a2").CurrentRegion.ClearContents
Set rs = cnn.Execute(sql)
For i = 0 To rs.fields.Count - 1
Sheet12.Cells(1, i + 1) = rs.fields(i).Name
Next
Sheet12.Range("a2").CopyFromRecordset rs
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub