VBA使用SQL查询表数据

一、VBA使用SQL查询表，统计数据

Sub 统计数据()

    Dim CNN  As Object
    Dim sql As String

     ThisWorkbook.Sheets("统计表").Activate
    With ThisWorkbook.Sheets("统计表")
        Cells.Clear
        Cells(1, 1) = "部门名称"
        Cells(1, 2) = "名单总人数"
    End With

    Set CNN = CreateObject("ADODB.Connection")
    With CNN
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
        .Open
    End With

    sql = "select 部门名称,count(工号) as 名单总人数 from [violate$A2:D65536]  where trim(工号)<>''  group by 部门名称"
    ThisWorkbook.Sheets("ViolateSum").Range("A2").CopyFromRecordset CNN.Execute(sql)

    CNN.Close
    Set CNN = Nothing

End Sub

二、用Left Join 连接两个以上的表

Sub 统计职工休假()
    Dim CNN  As Object
    Dim sql As String

    ThisWorkbook.Sheets("统计表").Activate
     With ThisWorkbook.Sheets("统计表")
        Cells(1, 15) = "部门"
        Cells(1, 16) = "总人数"
        Cells(1, 17) = "四天人数"
        Cells(1, 18) = "四天百分比"
        Cells(1, 19) = "三天人数"
        Cells(1, 20) = "三天百分比"

        Cells(1, 21) = "两天人数"
        Cells(1, 22) = "两天百分比"
        Cells(1, 23) = "零天人数"
        Cells(1, 24) = "零天百分比"
    End With

    sql = "Select A.部门名称,A.名单总人数,B.四天人数,四天人数/名单总人数,C.三天人数,三天人数/名单总人数 From " + _
          "([HolidaySum$A:B] A Left Join [统计表$C:D] B On A.部门名称=B.部门名称4" + _
          ") Left Join [统计表$F:G] C On A.部门名称=C.部门名称3"
    Sheets("统计表").Range("O2").CopyFromRecordset CNN.Execute(sql)

    sql = "Select B.两天人数,两天人数/名单总人数,C.未请假人数,未请假人数/名单总人数 From " + _
          "([统计表$A:B] A Left Join [统计表$I:J] B On A.部门名称=B.部门名称2" + _
          ") Left Join [统计表$L:M] C On A.部门名称=C.部门名称0"
    Sheets("统计表").Range("U2").CopyFromRecordset CNN.Execute(sql)

    Sheets("统计表").Range("A:N").Delete

    CNN.Close
    Set CNN = Nothing

End Sub