|
本帖最后由 freexun_zp 于 2013-12-31 21:16 编辑
老师们! 你们好!
最近遇见一个难题,怎么想也想不通,故来请老师们帮忙解惑,非常感谢!!‘
问题是这样的:
1. 我要做一个VBA多条件查询EXCEL中原始数据资料,需要查询条件有日期时间,工号,姓名(如附件)。
但是条件中有一个日期,时间起始条件, 原始资料中日期时间在EXCEL中的不同列显示,如何使用VBA查询时让原始数据资料中的日期时间合并并且达到需要查询资料???(不修改原始资料)
2.条件查询时有部分人员,工号是不需要提取的,如何让VBA查询时避开那部分人员(人员清单在表2) 达到查询要求??
以上求助各位老师帮忙解答,学生万分感谢!!!
改进下,查询的日期时间可有可无,条件任意组合。 - Option Explicit
- Const adUseClient = 3
- Const adModeRead = 1
- Const adStateOpen = 1
- Dim AdoConn As Object, AdoRst As Object
- Function OpenConnect(strFullname) As Boolean
- '---------------------------------------------------------------------------------------
- ' Procedure : OpenConnect
- ' Author : hwc2ycy
- ' Date : 2013-12-29
- ' Purpose : 建立数据链接,链接成功则返回TRUE,再次执行时,如果链接正常则不再重新建立。
- '---------------------------------------------------------------------------------------
- '
- Dim StrConn$
- On Error GoTo ErrorHandler
- If Not AdoConn Is Nothing Then
- If (AdoConn.State And adStateOpen) = adStateOpen Then
- OpenConnect = True
- Exit Function
- Else
- Set AdoConn = Nothing
- End If
- End If
- Set AdoConn = CreateObject("ADODB.Connection")
- Select Case Application.Version
- Case "14.0", "12.0", "16.0"
- StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source='" & _
- strFullname & "';Extended Properties='Excel 12.0;HDR=YES" & ";imex=1';"
- Case Else
- StrConn = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source='" & strFullname & "';Extended Properties='Excel 8.0;HDR=YES" & ";imex=1';"
- End Select
- With AdoConn
- .CommandTimeout = 5 '命令超时
- .ConnectionTimeout = 5 '连接超时
- .CursorLocation = adUseClient '光标类型
- .Mode = adModeRead '模式
- .ConnectionString = StrConn
- .Open
- End With
- OpenConnect = True
- Exit Function
- ErrorHandler:
- MsgBox Err.Number & vbCrLf & Err.Description
- Set AdoRst = Nothing
- End Function
- Sub Main()
- Dim strDatabase$
- Dim strSQL$, strID$, strName$
- Dim strSubSQL$, strDate$
- On Error GoTo ErrorHandler
- strDatabase = ThisWorkbook.FullName
- With Worksheets("异常人员查询")
- If Not OpenConnect(strDatabase) Then
- MsgBox "访问 " & strDatabase & " 失败" & vbCrLf & _
- "确定退出", vbCritical + vbOKOnly
- Exit Sub
- End If
- '建立连接
- Set AdoRst = CreateObject("adodb.recordset")
- strSQL = "select 單位名稱,班別,人員,工號,姓名,卡鐘代號,卡鐘位置,`進/出`,狀態,刷卡日期,時間,卡號 from [系統導出的出勤資料$a1:n" & Worksheets("系統導出的出勤資料").Cells(Rows.Count, 1).End(xlUp).Row & "] where "
- If Len(.Range("c3").Value) Then
- strDate = "刷卡日期+時間>=#" & .Range("c3").Value & "# and "
- '日期起始时间
- End If
- If Len(.Range("f3").Value) Then
- strDate = strDate & "刷卡日期+時間<=#" & .Range("f3").Value & "# and "
- '日期结束时间
- End If
-
- strSQL = strSQL & strDate
- '此种语句下要求C3是起始时间,F3是结束时间
-
- If Len(.[c5].Value) Then
- strID = "工號 like '" & .[c5].Value & "' and "
- '检测是否指定查询工号
- strSQL = strSQL & strID
- End If
- If Len(.[g5].Value) Then
- strName = "姓名 like '" & .[g5].Value & "' and "
- '检测是否指定查询姓名
- strSQL = strSQL & strName
- End If
- strSubSQL = "工號 not in (select 工號 from [不需要計算的人員$a1:a" & Worksheets("不需要計算的人員").Cells(Rows.Count, 1).End(xlUp).Row & "])"
- '嵌套查询,检测工号是号在排除名单之中
- strSQL = strSQL & strSubSQL
- 'Debug.Print strSQL
- AdoRst.Open strSQL, AdoConn, 2, 1
- MsgBox "完成" & vbCrLf & "一共 " & AdoRst.RecordCount & " 条记录"
- Application.ScreenUpdating = False
- If .Cells(Rows.Count, 2).End(xlUp).Row > 9 Then
- .Range("b9:m" & .Cells(Rows.Count, 2).End(xlUp).Row).ClearContents
- End If
- '清空单元格中原有内容
- .Range("b9").CopyFromRecordset AdoRst
- '查询结果写入单元格中
- .Columns("l").NumberFormatLocal = "[$-F400]h:mm:ss AM/PM"
- 'L列格式调整为时间
- Application.ScreenUpdating = True
- End With
- AdoRst.Close
- Exit Sub
- ErrorHandler:
- MsgBox Err.Number & vbCrLf & _
- Err.Description
- AdoConn.Close
- Set AdoConn = Nothing
- End Sub
复制代码
|
|