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