従業員になるべく負担をかけないで、出勤時間や退社時間をとにかく簡単に管理したいと思い、WindowsBATファイルとエクセルを使用した勤務管理表を考えました。
パソコンの起動時にBATファイルをキックしてLOGファイルを作成し、退社時にはデスクトップの退社BATを実行してもらうという仕様です。
作成するプログラム
１ ログインLOG収集BATファイル
ファイル名 sendloginlog.bat
@echo off
setlocal EnableDelayedExpansion
REM ログファイルのパス
set "logFile=?:\temp\log.csv"
REM ユーザ情報取得
set "username=%USERNAME%"
set "hostname=%COMPUTERNAME%"
REM 現在の日時を取得
for /f "delims=" %%a in ('wmic OS Get localdatetime ^| find "."') do set datetime=%%a
set "date=!datetime:~0,4!-!datetime:~4,2!-!datetime:~6,2!"
set "time=!datetime:~8,2!:!datetime:~10,2!:!datetime:~12,2!"
REM ログファイルに追記
echo %date%,%time%,!username!,!hostname!,LOGIN>> "%logFile%"
endlocal
logFileの場所には、使用者全員がアクセスできるネットワークドライブを指定してください。
sendlogoutlog.bat は、
REM ログファイルに追記 echo %date%,%time%,!username!,!hostname!,LOGOUT>> “%logFile%”
の部分が違うだけです
２ エクセルファイル（マクロ有効ファイル）
Range(C3)にログファイルパスを記入
青いボックスにmain()マクロを登録
Sub main()
' 出力用のシートを作成
Dim ws As Worksheet
Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
' 取得した件数
Dim lowCount As Integer
Dim logFile As String
'ファイル名の取得
logFile = Worksheets("main").Range("C3").Value
lowCount = ImportCSVFile(ws, logFile)
If lowCount = 0 Then
MsgBox ("指定されたファイルが見つからないか、LOGデータが見つかりませんでした。")
Exit Sub
End If
'計算用シートの作成
Dim sws As Worksheet
Set sws = CreateOrGetSheet("sorceSheet")
sws.Cells.Clear
' シートAからデータを取得し、シートBに反映させる
For i = 1 To lowCount
' 日付を取得
originalDate = CDate(ws.Cells(i, 1).Value)
' 日付の日（dd）を取得し、ユーザー名に追加
ModifiedDate = Format(originalDate, "dd")
sws.Cells(i, 1).Value = ws.Cells(i, 3).Value & "_" & ModifiedDate
sws.Cells(i, 2).Value = Format(originalDate, "yyyy/mm/dd")
sws.Cells(i, 3).Value = Format(ws.Cells(i, 2).Value, "hh:mm:ss")
sws.Cells(i, 4).Value = ws.Cells(i, 3).Value
sws.Cells(i, 5).Value = ws.Cells(i, 4).Value
sws.Cells(i, 6).Value = ws.Cells(i, 5).Value
Next i
'CVSシートは不要になるので削除
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End Sub
'CSVファイルを読込みtargetsheetに出力する
Function ImportCSVFile(targetSheet As Worksheet, csvFileName As String) As Integer
Dim queryTable As queryTable
Dim importedRowCount As Integer
' CSVファイルをワークシートにインポート
With targetSheet.QueryTables.Add(Connection:="TEXT;" & csvFileName, Destination:=Range("A1"))
.TextFileParseType = xlDelimited
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True ' CSVの区切り文字がカンマの場合
.Refresh
importedRowCount = .ResultRange.Rows.Count
End With
' インポートした行数を返す
ImportCSVFile = importedRowCount
End Function
'名前を指定して新しいシートインスタンスを取得する（同じ名前のシートがあれば、そのシートを取得)
' 名前を指定して新しいシートインスタンスを取得する（同じ名前のシートがあれば、そのシートを取得)
Function CreateOrGetSheet(sheetName As String) As Worksheet
Dim ws As Worksheet
' 指定した名前のシートがすでに存在する場合は取得して返す
For Each ws In ThisWorkbook.Sheets
If ws.Name = sheetName Then
Set CreateOrGetSheet = ws
Exit Function
End If
Next ws
' 指定した名前のシートが存在しない場合は新しいシートを作成して返す
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = sheetName
Set CreateOrGetSheet = ws
End Function
'VLOOUKP関数のカスタム関数
'該当無しの場合にerrorStringを返します
Function EvLookup(lookup_value As Variant, table_array As Range, col_index_num As Long, errorString As String) As Variant
On Error Resume Next
' VLOOKUP関数を使用してデータを検索
Dim result As Variant
result = Application.WorksheetFunction.VLookup(lookup_value, table_array, col_index_num, False)
On Error GoTo 0
' エラーチェックしてエラーならerrorStringを返す
If IsError(result) Then
EvLookup = errorString
Else
EvLookup = result
End If
End Function
２ 報告書
出勤報告書は、それぞれの環境で異なると思いますので、[sourcesheet]の内容を反映するような報告書を作成すればいいと思います。
サンプルの出勤報告書
B5セルに
=EVLOOKUP(B$3&”“&$A5&”“&B$4,sorceSheet!$A$1:$E$9999,3,””)
という関数が入っていますのでこれを他のセルにコピペすればいいです
配置編
sendloginlog.bat は、それぞれのパソコンのスタートアップフォルダに配置
※エクスプローラのPATHに 「shell:startup」と入力するとスタートアップフォルダが開きます
sendlogoutlog.bat、それぞれのパソコンのデスクトップに置いて、
「PCを切断する前にダブルクリックしてね」
と伝えてください。
本当はログオフイベントでキックさせたかったのですが、タスクからの呼び出しがわかりにくかったので、要検討です。
