Sub test()
Application.CommandBars("cell").Reset
End Sub
Sub auto_open()
On Error Resume Next
Application.CommandBars("cell").Controls("TC集計開始").Delete
Application.CommandBars("cell").Controls("TC集計開始").Delete
With Application.CommandBars("cell").Controls.Add
.OnAction = "main"
.Caption = "TC集計開始"
End With
Application.CommandBars("cell").Controls("確認表示").Delete
Application.CommandBars("cell").Controls("確認表示").Delete
With Application.CommandBars("cell").Controls.Add
.OnAction = "check_layout"
.Caption = "確認表示"
End With
End Sub
Sub auto_close()
On Error Resume Next
Application.CommandBars("cell").Controls("TC集計開始").Delete
Application.CommandBars("cell").Controls("TC集計開始").Delete
Application.CommandBars("cell").Controls("確認表示").Delete
Application.CommandBars("cell").Controls("確認表示").Delete
End Sub
Sub check_layout()
tmp = MsgBox("実行後に保存しないよう注意してください。実行しますか?", vbYesNo)
If tmp = vbNo Then Exit Sub
For Each w In Worksheets
If w.Name <> "MAIN MENU" Then
w.Columns("A:X").ColumnWidth = 0
w.Rows("1:3").RowHeight = 0
End If
Next
MsgBox "終了しました。"
End Sub
Sub init()
For Each w In Worksheets
If w.Name <> "MAIN MENU" Then
w.Activate
w.Unprotect
w.Range("W4").Select
ActiveWindow.Zoom = 85
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
w.Range("B5:B35").ClearContents
w.Range("C5:C35").ClearContents
w.Range("E5:E35").ClearContents
w.Range("F5:F35").ClearContents
w.Range("S5:S35").ClearContents
w.Range("W4:AK35").ClearContents
w.Range("B5:C35").NumberFormatLocal = "0"
w.Range("E5:F35").NumberFormatLocal = "0"
w.Range("N5:Q35").NumberFormatLocal = "0.00"
w.Range("W5:W35").NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"
w.Range("X5:X35").NumberFormatLocal = "0"
w.Range("Z5:AD35").NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"
w.Range("AE5:AK35").NumberFormatLocal = "@"
For r = 5 To 35
w.Cells(r, 2).Value = "=HOUR(AA" & r & ")"
w.Cells(r, 3).Value = "=MINUTE(AA" & r & ")"
w.Cells(r, 5).Value = "=HOUR(AB" & r & ") + (INT(AB" & r & ")-INT(AA" & r & ")) * 24"
w.Cells(r, 6).Value = "=MINUTE(AB" & r & ")"
w.Cells(r, 14).Value = "=IF(D" & r & "="""","""",(IF(AND(VALUE(D" & r & ")<VALUE(""7:31""),VALUE(D" & r & ")>=VALUE(""6:31"")),1,IF(AND(VALUE(D" & r & ")<VALUE(""6:31""),VALUE(D" & r & ")>=VALUE(""5:31"")),2,IF(AND(VALUE(D" & r & ")<VALUE(""5:31""),VALUE(D" & r & ")>=VALUE(""4:30"")),3,0)))))"
'w.Cells(r, 15).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(D" & r & ")<=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:21"")),8,IF(AND(NOT(VALUE(D" & r & ")<=VALUE(""8:30"")),NOT(VALUE(G" & r & ")>VALUE(""17:21""))),FLOOR(H" & r & "*24-I" & r & ",0.5),IF(AND(VALUE(D" & r & ")>=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:21"")),IF(FLOOR(G" & r & "*24-D" & r & "*24-I" & r & ",0.5)>4,4,FLOOR(G" & r & "*24-D" & r & "*24-I" & r & ",0.5)),IF(VALUE(G" & r & ")<=VALUE(""17:21""),FLOOR(VALUE(G" & r & ")*24-(""8:30"")*24-I" & r & ",0.5))))))"
'w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(M" & r & "="""",IF(VALUE(G" & r & ")>VALUE(""18:30""),IF(AND(-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)>2,S" & r & "=""申請不要""),2,-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)),0),IF(VALUE(G" & r & ")>VALUE(""18:30""),IF(AND(-Q" & r & "+FLOOR(G" & r & "*24-""17:31""*24,0.5)>2,S" & r & "=""申請不要""),2,-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)),0)))"
'w.Cells(r, 17).Value = "=IF(H" & r & "="""","""",(IF(VALUE(G" & r & ")*24>TIMEVALUE(""22:01"")*24,FLOOR(G" & r & "-""22:01"",""0:30"")*24,0)))"
' rev2.3 2016/4/26追加
w.Cells(r, 15).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(D" & r & ")<=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:20"")),8,IF(AND(VALUE(D" & r & ")>VALUE(""8:30""),VALUE(G" & r & ")<VALUE(""17:20"")),FLOOR(VALUE(H" & r & ")*24-I" & r & ",0.25),IF(AND(VALUE(D" & r & ")>VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:20"")),FLOOR((VALUE(G" & r & ")*24-VALUE(D" & r & ")*24)-I" & r & ",0.25),FLOOR((VALUE(G" & r & ")*24-VALUE(""8:30"")*24)-I" & r & ",0.25)))))"
'w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(G" & r & ")>VALUE(""18:30""),S" & r & "<>""申請不要""),IF(M" & r & "="""",-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""18:00"")*24,0.25),-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:30"")*24,0.25)),0))"
w.Cells(r, 17).Value = "=IF(H" & r & "="""","""",IF(VALUE(G" & r & ")*24>VALUE(""22:00"")*24,FLOOR(VALUE(G" & r & ")*24-VALUE(""22:00"")*24,0.25),0))"
' rev2.5.1
w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(G" & r & ")>VALUE(""18:14:59""),S" & r & "<>""申請不要""),IF(M" & r & "="""",-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:59:59"")*24,0.25),-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:29:59"")*24,0.25)),0))"
Next r
DoEvents
End If
Next w
End Sub
Sub ed()
For Each w In Worksheets
If w.Name <> "MAIN MENU" Then
w.Activate
For r = 5 To 35
If w.Cells(r, 27).Value = "" Then w.Cells(r, 2).Value = ""
If w.Cells(r, 27).Value = "" Then w.Cells(r, 3).Value = ""
If w.Cells(r, 28).Value = "" Then w.Cells(r, 5).Value = ""
If w.Cells(r, 28).Value = "" Then w.Cells(r, 6).Value = ""
If w.Cells(r, 31).Value = "" And w.Cells(r, 13).Text = "" Then
w.Cells(r, 14).Value = ""
End If
If w.Cells(r, 32).Value = "" And w.Cells(r, 13).Text = "" Then
w.Cells(r, 16).Value = ""
w.Cells(r, 17).Value = ""
End If
If w.Cells(r, 33).Value = "" And w.Cells(r, 13).Text <> "" Then
w.Cells(r, 14).Value = ""
w.Cells(r, 15).Value = ""
w.Cells(r, 16).Value = ""
w.Cells(r, 17).Value = ""
End If
Select Case w.Name
Case "xxx"
Case Else
If w.Cells(r, 13).Text = "" Then w.Cells(r, 15).NumberFormatLocal = """-"""
End Select
Next r
DoEvents
End If
w.Columns("W:AK").AutoFit
Next w
End Sub
Sub ck()
For Each w In Worksheets
If w.Name <> "MAIN MENU" Then
For r = 5 To 35
Select Case w.Name
Case "xxx"
Case Else
If IsError(w.Cells(r, 15)) Then
w.Activate: w.Cells(r, 15).Select
MsgBox w.Name & "の" & r & "行目の定時時間が正しく取得できません。"
Else
If w.Cells(r, 13).Value = "" And w.Cells(r, 15).Value <> "" And w.Cells(r, 15).Value < 8 Then
If w.Cells(r, 19).Value = "" Then
w.Activate: w.Cells(r, 15).Select
MsgBox w.Name & "の" & r & "行目の定時時間が不足しています。" & Chr(13) & "申請がありません。"
ElseIf w.Cells(r, 19).Value <> "" And w.Cells(r, 19).Value <> "有給" And w.Cells(r, 19).Value <> "半休" Then
w.Activate: w.Cells(r, 15).Select
MsgBox w.Name & "の" & r & "行目の定時時間が不足しています。" & Chr(13) & "有給・半休が申請されていません。"
End If
End If
End If
End Select
Next r
End If
Next w
End Sub
Private Sub main()
Call init
f = "#" & Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1 00:00:01#"
tmp = DateAdd("d", -1, DateAdd("m", 1, Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1"))
t = "#" & tmp & " 00:00:01#"
For Each w In Worksheets
If w.Name <> "MAIN MENU" Then
w.Activate
If False = fnc(w.Name, f, t) Then Exit Sub
DoEvents
End If
Next w
Call ed
Call ck
MsgBox "done"
End Sub
Function n(fld As Variant)
If False = IsNull(fld) Then
n = CStr(fld)
Exit Function
End If
n = ""
End Function
Function fnc(u, f, t)
On Error GoTo try
p = ActiveWorkbook.Path & "\Tc_Db.mdb"
s = "SELECT * FROM Tc_Tbl WHERE 氏名 = '" & u & "' AND 日付 BETWEEN " & f & " AND " & t & ""
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & p & ";"
rs.Open s, cn
k = 0
tmp = Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1 00:00:01"
For i = 5 To 35
If Month(tmp) = Month(DateAdd("d", k, tmp)) Then Sheets(u).Cells(i, 23) = DateAdd("d", k, tmp)
k = k + 1
Next
For i = 24 To 37
Sheets(u).Cells(4, i) = rs(i - 24).Name
Next i
Do Until rs.EOF
For r = 5 To 35
If Sheets(u).Cells(r, 23) <> "" And Day(Sheets(u).Cells(r, 23)) = Day(n(rs(2))) Then
For c = 24 To 37
Sheets(u).Cells(r, c).Value = n(rs(c - 24))
Next c
If Sheets(u).Cells(r, 31).Value <> "" Then Sheets(u).Cells(r, 19).Value = "早出申請有"
If Sheets(u).Cells(r, 32).Value <> "" Then Sheets(u).Cells(r, 19).Value = "残業申請有"
If Sheets(u).Cells(r, 31).Value <> "" And Sheets(u).Cells(r, 32).Value <> "" Then Sheets(u).Cells(r, 19).Value = "残・早申請有"
If Sheets(u).Cells(r, 33).Value <> "" Then Sheets(u).Cells(r, 19).Value = "休日出勤申請有"
If Sheets(u).Cells(r, 34).Value <> "" Then Sheets(u).Cells(r, 19).Value = "有休"
If InStr(Sheets(u).Cells(r, 34).Value, "(") > 0 Then Sheets(u).Cells(r, 19).Value = "半休"
If Sheets(u).Cells(r, 35).Value <> "" Then Sheets(u).Cells(r, 19).Value = "欠勤"
If Sheets(u).Cells(r, 36).Value <> "" Then Sheets(u).Cells(r, 19).Value = "代休"
If InStr(Sheets(u).Cells(r, 37).Value, "申請不要") > 0 Then Sheets(u).Cells(r, 19).Value = "申請不要"
End If
Next r
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
fnc = True
Exit Function
try:
MsgBox "error"
fnc = False
End Function