|
Sub 生成最低()
Application.ScreenUpdating = False
[A2:K132].ClearContents
提取3A
提取3B
提取3C
提取2G
提取2H
提取2J
提取A1
提取A2
提取B1
插入最低
Application.ScreenUpdating = True
MsgBox "OK,提取完毕"
End Sub
Sub 插入最低()
Application.ScreenUpdating = False
Columns("F:H").Insert Shift:=xlToRight '当日收入前插入三行
[A1] = "部门"
[B1] = "工号"
[C1] = "组别"
[D1] = "姓名"
[E1] = "入厂日期"
[F1] = "工龄"
[G1] = "月收入"
[H1] = "前一天收入"
[I1] = "当日收入"
[J1] = "上班小时"
[K1] = "平均时薪"
[L1:IV1].ClearContents
Application.ScreenUpdating = True
End Sub
Sub 提取3A()
Application.ScreenUpdating = False
Set conn = CreateObject("ADODB.Connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes';data source=" & ThisWorkbook.FullName
SQL = "select top 10 车间,工号,组别,姓名,入厂日期,金额,上班小时,平均时薪 from [明细$] where 车间 = '3A' and 上班小时 >= 8 order by 金额-序号/10000 "
'SQL是设置条件语句,hdr=YES,是默认第一行是标题,有标题的,hdr=NO,是没有设置标题的,字段用F1,F1,F3,F(N)代替。
Range("A2").CopyFromRecordset conn.Execute(SQL)
Range("A12") = "3A 10人"
Set conn = Nothing
Application.ScreenUpdating = True
'MsgBox "OK,提取完毕"
End Sub
Sub 提取3B()
Application.ScreenUpdating = False
'[A2:O65536].ClearContents
Set conn = CreateObject("ADODB.Connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes';data source=" & ThisWorkbook.FullName
SQL = "select top 10 车间,工号,组别,姓名,入厂日期,金额,上班小时,平均时薪 from [明细$] where 车间 = '3B' and 上班小时 >= 8 order by 金额-序号/10000 "
'SQL是设置条件语句,hdr=YES,是默认第一行是标题,有标题的,hdr=NO,是没有设置标题的,字段用F1,F1,F3,F(N)代替。
Range("A13").CopyFromRecordset conn.Execute(SQL)
Range("A23") = "3B 10人"
Set conn = Nothing
Application.ScreenUpdating = True
'MsgBox "OK,提取完毕"
End Sub
Sub 提取3C()
Application.ScreenUpdating = False
Set conn = CreateObject("ADODB.Connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes';data source=" & ThisWorkbook.FullName
SQL = "select top 10 车间,工号,组别,姓名,入厂日期,金额,上班小时,平均时薪 from [明细$] where 车间 = '3C' and 上班小时 >= 8 order by 金额-序号/10000 "
'SQL是设置条件语句,hdr=YES,是默认第一行是标题,有标题的,hdr=NO,是没有设置标题的,字段用F1,F1,F3,F(N)代替。
Range("A24").CopyFromRecordset conn.Execute(SQL)
Range("A34") = "3C 10人"
Set conn = Nothing
Application.ScreenUpdating = True
'MsgBox "OK,提取完毕"
End Sub
Sub 提取2G()
Application.ScreenUpdating = False
Set conn = CreateObject("ADODB.Connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes';data source=" & ThisWorkbook.FullName
SQL = "select top 10 车间,工号,组别,姓名,入厂日期,金额,上班小时,平均时薪 from [明细$] where 车间 = '2G' and 上班小时 >= 8 order by 金额-序号/10000 "
'SQL是设置条件语句,hdr=YES,是默认第一行是标题,有标题的,hdr=NO,是没有设置标题的,字段用F1,F1,F3,F(N)代替。
Range("A35").CopyFromRecordset conn.Execute(SQL)
Range("A45") = "2G 10人"
Set conn = Nothing
Application.ScreenUpdating = True
'MsgBox "OK,提取完毕"
End Sub
Sub 提取2H()
Application.ScreenUpdating = False
Set conn = CreateObject("ADODB.Connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes';data source=" & ThisWorkbook.FullName
SQL = "select top 10 车间,工号,组别,姓名,入厂日期,金额,上班小时,平均时薪 from [明细$] where 车间 = '2H' and 上班小时 >= 8 order by 金额-序号/10000 "
'SQL是设置条件语句,hdr=YES,是默认第一行是标题,有标题的,hdr=NO,是没有设置标题的,字段用F1,F1,F3,F(N)代替。
Range("A46").CopyFromRecordset conn.Execute(SQL)
Range("A56") = "2H 10人"
Set conn = Nothing
Application.ScreenUpdating = True
'MsgBox "OK,提取完毕"
End Sub
Sub 提取2J()
Application.ScreenUpdating = False
Set conn = CreateObject("ADODB.Connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes';data source=" & ThisWorkbook.FullName
SQL = "select top 10 车间,工号,组别,姓名,入厂日期,金额,上班小时,平均时薪 from [明细$] where 车间 = '2J' and 上班小时 >= 8 order by 金额-序号/10000 "
'SQL是设置条件语句,hdr=YES,是默认第一行是标题,有标题的,hdr=NO,是没有设置标题的,字段用F1,F1,F3,F(N)代替。
Range("A57").CopyFromRecordset conn.Execute(SQL)
Range("A67") = "2J 10人"
Set conn = Nothing
Application.ScreenUpdating = True
'MsgBox "OK,提取完毕"
End Sub
提取,提取,提取,.......要是还有十多个,不累死才怪,这十多个提取都可以并做一个,一次性完成,代码确实多,方法:为每个部门所有人员给定一个排序号,限定提取前10名就成,十多个模块只用一个模块就可以达到要求的
SQL = "select top 10 车间,工号,组别,姓名,入厂日期,金额,上班小时,平均时薪 from [明细$] where 车间 = '2H' and 上班小时 >= 8 o 想帮你,但怕你反感我,只好提个醒
|
|