|
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 貨號(hào),品名,sum(數(shù)量) as 數(shù)量匯總 from [明細(xì)表$B1:E765] group by 貨號(hào),品名,數(shù)量 order by sum(數(shù)量) 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 |
|
|
