VBA与数据库——简化程序编写-汇总

Sub vba_main()
    Dim arr() As Variant
    Dim i_row As Long

    '获取最后一行的行号
    i_row = Cells(Cells.Rows.Count, 1).End(xlUp).Row
    '读取数据
    arr = Range("A1").Resize(i_row, 4).Value
    
    '声明字典对象
    Dim dic As Object
    Set dic = VBA.CreateObject("Scripting.Dictionary")

    Dim i As Long
    '循环统计，项目作为字典的key，统计的数据作为item
    For i = 2 To i_row
        dic(VBA.CStr(arr(i, 2))) = dic(VBA.CStr(arr(i, 2))) + VBA.CDbl(arr(i, 3))
    Next
    
    Dim keys As Variant, items As Variant
    keys = dic.keys()
    items = dic.items()
    '输出
    Range("F1").Resize(UBound(keys) + 1, 1).Value = Application.WorksheetFunction.Transpose(keys)
    Range("G1").Resize(UBound(items) + 1, 1).Value = Application.WorksheetFunction.Transpose(items)
End Sub

dic(VBA.CStr(arr(i, 2))) = dic(VBA.CStr(arr(i, 2))) + VBA.CDbl(arr(i, 3))

Sub Test()
    Dim AdoConn As Object
    Set AdoConn = VBA.CreateObject("ADODB.Connection")
    
    '打开数据库
    AdoConn.Open "Provider =Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";"
    Range("F1").CopyFromRecordset AdoConn.Execute("select 项目,Sum(数据) from [Sheet2$] group by 项目", , 1)

    AdoConn.Close
    Set AdoConn = Nothing
End Sub

Range("F1").CopyFromRecordset AdoConn.Execute("select 项目,Sum(数据) from [Sheet2$] group by 项目", , 1)

Range("F1").CopyFromRecordset AdoConn.Execute("select 条件1,条件2,Sum(数据) from [Sheet2$] group by 条件1,条件2", , 1)