|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
谢谢上楼的代码,整理如下:
问题2代码。
Sub bTest4统计年龄段人数()
Dim Conn As Object, Rst As Object
Dim strConn As String, strSQL As String
Dim i As Integer, PathStr As String
Set Conn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
PathStr = ThisWorkbook.FullName '设置工作簿的完整路径和名称
Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接
Case Is <= 11
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
'设置SQL查询语句
CountMax = 36
CountMin = 85
strSQL = "select 分段,count(*) as 人数 from " _
& " (select Partition(年龄," & CountMax & "," & CountMin & ",5) as 分段 From" _
& " (select year(date())-year(出生年月) as 年龄 From [sheet1$A1:I])) group by 分段"
Conn.Open strConn '打开数据库链接
Set Rst = Conn.Execute(strSQL) '执行查询,并将结果输出到记录集对象
With Sheet3.Range("f:g")
.Cells.Clear
For i = 0 To Rst.Fields.Count - 1 '填写标题
.Cells(1, i + 1) = Rst.Fields(i).Name
Next i
.Range("A2").CopyFromRecordset Rst
.Cells.EntireColumn.AutoFit '自动调整列宽
End With
Rst.Close '关闭数据库连接
Conn.Close
Set Conn = Nothing
Set Rst = Nothing
End Sub
问题3代码。
Sub cTest4按部门统计年龄段人数()
Dim Conn As Object, Rst As Object
Dim strConn As String, strSQL As String
Dim i As Integer, PathStr As String
Set Conn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
PathStr = ThisWorkbook.FullName '设置工作簿的完整路径和名称
Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接
Case Is <= 11
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
'设置SQL查询语句
CountMax = 36
CountMin = 85
strSQL = "select 部门,分段,count(*) as 人数 from " _
& " (select 部门,Partition(年龄," & CountMax & "," & CountMin & ",5) as 分段 From" _
& " (select 部门,year(Date())-year(出生年月) as 年龄 From [sheet1$A1:I])) group by 部门,分段"
Conn.Open strConn '打开数据库链接
Set Rst = Conn.Execute(strSQL) '执行查询,并将结果输出到记录集对象
With Sheet3.Range("b:d")
.Cells.Clear
For i = 0 To Rst.Fields.Count - 1 '填写标题
.Cells(1, i + 1) = Rst.Fields(i).Name
Next i
.Range("A2").CopyFromRecordset Rst
.Cells.EntireColumn.AutoFit '自动调整列宽
End With
Rst.Close '关闭数据库连接
Conn.Close
Set Conn = Nothing
Set Rst = Nothing
End Sub
|
|