一、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 统计职工休假()