VBA使用SQL查詢表數(shù)據(jù)

一、VBA使用SQL查詢表，統(tǒng)計(jì)數(shù)據(jù)

Sub 統(tǒng)計(jì)數(shù)據(jù)()

    Dim CNN  As Object
    Dim sql As String

     ThisWorkbook.Sheets("統(tǒng)計(jì)表").Activate
    With ThisWorkbook.Sheets("統(tǒng)計(jì)表")
        Cells.Clear
        Cells(1, 1) = "部門名稱"
        Cells(1, 2) = "名單總?cè)藬?shù)"
    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(工號(hào)) as 名單總?cè)藬?shù) from [violate$A2:D65536]  where trim(工號(hào))<>''  group by 部門名稱"
    ThisWorkbook.Sheets("ViolateSum").Range("A2").CopyFromRecordset CNN.Execute(sql)

    CNN.Close
    Set CNN = Nothing

End Sub

二、用Left Join 連接兩個(gè)以上的表

Sub 統(tǒng)計(jì)職工休假()
    Dim CNN  As Object
    Dim sql As String

    ThisWorkbook.Sheets("統(tǒng)計(jì)表").Activate
     With ThisWorkbook.Sheets("統(tǒng)計(jì)表")
        Cells(1, 15) = "部門"
        Cells(1, 16) = "總?cè)藬?shù)"
        Cells(1, 17) = "四天人數(shù)"
        Cells(1, 18) = "四天百分比"
        Cells(1, 19) = "三天人數(shù)"
        Cells(1, 20) = "三天百分比"

        Cells(1, 21) = "兩天人數(shù)"
        Cells(1, 22) = "兩天百分比"
        Cells(1, 23) = "零天人數(shù)"
        Cells(1, 24) = "零天百分比"
    End With

    sql = "Select A.部門名稱,A.名單總?cè)藬?shù),B.四天人數(shù),四天人數(shù)/名單總?cè)藬?shù),C.三天人數(shù),三天人數(shù)/名單總?cè)藬?shù) From " + _
          "([HolidaySum$A:B] A Left Join [統(tǒng)計(jì)表$C:D] B On A.部門名稱=B.部門名稱4" + _
          ") Left Join [統(tǒng)計(jì)表$F:G] C On A.部門名稱=C.部門名稱3"
    Sheets("統(tǒng)計(jì)表").Range("O2").CopyFromRecordset CNN.Execute(sql)

    sql = "Select B.兩天人數(shù),兩天人數(shù)/名單總?cè)藬?shù),C.未請(qǐng)假人數(shù),未請(qǐng)假人數(shù)/名單總?cè)藬?shù) From " + _
          "([統(tǒng)計(jì)表$A:B] A Left Join [統(tǒng)計(jì)表$I:J] B On A.部門名稱=B.部門名稱2" + _
          ") Left Join [統(tǒng)計(jì)表$L:M] C On A.部門名稱=C.部門名稱0"
    Sheets("統(tǒng)計(jì)表").Range("U2").CopyFromRecordset CNN.Execute(sql)

    Sheets("統(tǒng)計(jì)表").Range("A:N").Delete

    CNN.Close
    Set CNN = Nothing

End Sub