'******************************************************************* '功能:加班数据回写函数。 '作者:刘相涛 '参数:加班申请文件,考勤数据文件,当前记录行,发起人姓名,考勤日期 '出力:将加班申请记录回写到考勤数据中 '******************************************************************* Function OverTimeData(HRName_overtime As String, HRName_system As String, Overtime_cnt As Integer, NowNum As Integer, Name As String, HRData As String) Dim m As Integer, SumRange1 As Integer, Overtime_i As Integer Dim thisworkbook As Workbook, thisworksheet As Worksheet Dim FunDingName As String, FunDingStartTime As String, FunDingStartDate As String, FunDingEndTime As String, FunDingEndDate As String, FunDays As String Set thisworkbook = Workbooks(HRName_overtime) Overtime_i = 1 Do While Overtime_i <= Overtime_cnt '将钉钉导出的加班数据文件作为当前的处理对象 Set thisworksheet = thisworkbook.Sheets(Overtime_i) m = 1 '统计钉钉导出加班数据记录数(含header) SumRange1 = 1 '统计钉钉导出数据行数 Do While thisworksheet.Cells(m, 1).Value <> "" m = m + 1 Loop '去header SumRange1 = m - 1 If SumRange1 = 1 Then MsgBox ("钉钉导出的加班数据为空,请确认!") End If '循环处理对象文件数据 For m = 2 To SumRange1 '发起人姓名 FunDingName = Trim(thisworksheet.Cells(m, 8).Value) '加班申请开始时间 FunDingStartTime = Trim(thisworksheet.Cells(m, 14).Value) '加班申请开始日期 FunDingStartDate = Left(FunDingStartTime, 10) '加班申请结束时间 FunDingEndTime = Trim(thisworksheet.Cells(m, 15).Value) '加班申请结束日期 FunDingEndDate = Left(FunDingEndTime, 10) '加班假时长(单位:小时) FunDays = Replace(Replace(Replace(Trim(thisworksheet.Cells(m, 16).Value), "小时", ""), "H", ""), "h", "") '钉钉数据和考勤数据匹配的条件:姓名,考勤日期 If Name = FunDingName And CDate(HRData) >= CDate(FunDingStartDate) And CDate(HRData) <= CDate(FunDingEndDate) Then Set thisworkbook = Workbooks(HRName_system) '将加班时长回写到对应的考勤数据中 thisworkbook.Worksheets(1).Cells(NowNum, 11).Value = "加班:" & FunDays & "小时" Exit For End If Next m Overtime_i = Overtime_i + 1 Set thisworkbook = Workbooks(HRName_overtime) Loop End Function
©️公众号:思考者文刀
- 上一篇: 「砥砺阅读」之十六《暗访十年(二)》
- 下一篇: VBA:考勤数据分析
评论