有給残数確認に時間をかけすぎてない！？エクセルVBAで別ブックから有給残数を自動取得する方法とは

職員全員の有給残数を確認するために、1人ずつ勤怠表を開いて情報を確認する作業を毎日続けていませんか？

VBAを使えば、複数の勤怠表ブックを自動で開いて有給残数を取得し、一覧表にまとめる作業をボタン1クリックで処理することが可能です。

この記事では、別ブックを開いて情報を取得するVBAの基本から、実務で使えるサンプルコードまでを順番に解説します。

有給管理の担当者として、毎日の確認作業に時間をかけている方はぜひ最後まで読んでみてください。

1. 手作業で有給残数を確認するとどんな問題が起きるのか
2. 別ブックを開いて情報を取得するVBAの基本を理解する
- 2.1. 別ブックを開いて情報を取得する基本コード
3. 職員全員の有給残数を一括取得するVBAの実装方法
4. 処理速度を大幅にアップさせる3つのテクニック
5. フォルダ内のすべての勤怠表ブックを自動処理するVBA
6. VBAを実行するボタンを設置して毎日の作業を1クリックで完了させる
7. 別ブック操作VBAで注意すべき3つのポイント
8. まとめ～別ブックから有給残数を自動取得するVBAで毎日の作業を効率化する～

手作業で有給残数を確認するとどんな問題が起きるのか

有給残数の確認作業は、一見シンプルに見えて実は多くの問題を抱えています。

まずは手作業で有給残数を管理することの問題点を整理してみましょう。

問題①：職員数が増えるほど確認作業に時間がかかる

職員が10人のうちはまだ手作業でも許容範囲かもしれません。

しかし職員が30人、50人と増えていくと、1人ずつ勤怠表ブックを開いて確認して閉じる、この繰り返しだけで30分以上かかることも珍しくありません。

私がマクロ開発を依頼された案件でも、「職員が増えて有給確認だけで毎朝1時間近くかかっている」という状況がほとんど。

毎日1時間の作業が積み重なると、1ヶ月で約20時間のロスです。

年間に換算すると240時間もの作業時間が有給確認だけに費やされていることになります。

VBAを導入して、ボタン１クリックで処理できるようにしましょう。

問題②：確認ミスが発生すると有給管理に重大な影響を及ぼす

ブックを何度も開いたり閉じたりしながら数値を手入力していると、どうしても転記ミスが発生します。

有給残数の確認ミスは「有給を取得できると思っていたのに実は残数がゼロだった」「残数が多いと思い込んでいたら計算が間違っていた」という事態を招き、労務管理上の重大な問題に発展しかねません。

単なるエクセル操作ミスでは済まされない可能性があるので、早急に対策しましょう。

問題③：毎日同じ単純作業を繰り返す非効率さ

有給残数の確認は、毎日または毎週決まったタイミングで行うルーティーン作業です。

毎回同じ手順で確認するにもかかわらず、手動で行うと毎回人間が操作をしなければなりません。

こういったルーティーン作業は、VBAで自動化してしまいましょう。

別ブックを開いて情報を取得するVBAの基本を理解する

別ブックから情報を取得するVBAは、Workbooksオブジェクトを使用します。

まずは基本的な考え方から押さえておきましょう。

VBAで別のエクセルファイル（ブック）を操作するには、大きく分けて2つの方法があります。

1つ目は、対象のブックをVBAで開いてから情報を取得する方法。

2つ目は、対象のブックを開かずに直接データを参照する方法（GetObject関数を使う方法）。

実務では、1つ目の「開いてから取得する方法」のほうがトラブルが少なく、コードも理解しやすいので、この記事では開いてから取得する方法を中心に解説します。

別ブックを開いて情報を取得する基本コード

まずは、指定したファイルパスのブックを開き、特定のセルの値を取得してから閉じるという、最もシンプルなサンプルコードを見てみましょう。

Sub 別ブックからデータ取得_基本()

Dim targetPath As String
Dim targetWb As Workbook
Dim targetWs As Worksheet
Dim getValue As String

'取得したいブックのファイルパスを指定
targetPath = "C:\勤怠管理\田中太郎_勤怠表.xlsx"

'対象ブックを開く（画面更新をオフにして見た目をスッキリさせる）
Application.ScreenUpdating = False
Set targetWb = Workbooks.Open(targetPath)

'対象シートを指定
Set targetWs = targetWb.Worksheets("勤怠")

'有給残数が入力されているセル（例：E30）を取得
getValue = targetWs.Range("E30").Value

'対象ブックを保存せずに閉じる
targetWb.Close SaveChanges:=False

'画面更新を元に戻す
Application.ScreenUpdating = True

'取得した値を表示（確認用）
MsgBox "有給残数：" & getValue & "日", vbInformation

End Sub

このコードの流れを解説します。

Application.ScreenUpdating = False は、VBAの実行中にエクセル画面がチカチカと切り替わるのを防ぐための設定です。

この1行があるだけで、処理のスピードが体感でわかるほど速くなります。

Workbooks.Open(targetPath) で指定したパスのブックを開き、Set targetWb でそのブックを変数に格納します。

変数に格納することで、以降のコードでは targetWb と書くだけでそのブックを指定できるようになります。

targetWb.Close SaveChanges:=False は、データを取得したあとにブックを保存せずに閉じる命令です。

SaveChanges:=False を忘れると「保存しますか？」というダイアログが表示されてしまい、自動処理が止まってしまうので必ず入れておきましょう。

職員全員の有給残数を一括取得するVBAの実装方法

基本的な別ブック操作を理解したところで、実務で使える「職員全員の有給残数を一括取得するVBA」を作っていきましょう。

以下のような状況を想定してサンプルコードを作成します。

フォルダ「C:\勤怠管理」の中に、職員ごとの勤怠表ブック（例：田中太郎_勤怠表.xlsx）が保存されている。

管理用のエクセルファイル（マスターブック）のシート「有給一覧」のA列に職員名が一覧になっており、B列に有給残数を転記したい。

各勤怠表ブックの「勤怠」シートのE30セルに有給残数が入力されている。

職員一覧から有給残数を一括取得するサンプルコード

Sub 有給残数一括取得()

Dim masterWs As Worksheet
Dim targetWb As Workbook
Dim targetWs As Worksheet
Dim folderPath As String
Dim fileName As String
Dim lastRow As Long
Dim i As Long
Dim staffName As String
Dim nenkyuZan As Variant

'処理対象シートを指定
Set masterWs = ThisWorkbook.Worksheets("有給一覧")

'勤怠表が保存されているフォルダパス
folderPath = "C:\勤怠管理\"

'画面更新をオフ（処理を高速化）
Application.ScreenUpdating = False

'職員名が入力されている最終行を取得
lastRow = masterWs.Cells(Rows.Count, 1).End(xlUp).Row

'2行目から最終行まで繰り返す
For i = 2 To lastRow

'職員名を取得
staffName = masterWs.Cells(i, 1).Value

'職員名が空欄の場合はスキップ
If staffName = "" Then GoTo NextRow

'勤怠表ブックのファイル名を組み立てる
fileName = folderPath & staffName & "_勤怠表.xlsx"

'指定したファイルが存在するか確認
If Dir(fileName) = "" Then
'存在しない場合はB列に「ファイルなし」と入力してスキップ
masterWs.Cells(i, 2).Value = "ファイルなし"
GoTo NextRow
End If

'エラーが発生した場合の処理
On Error GoTo ErrorHandle

'対象ブックを開く
Set targetWb = Workbooks.Open(fileName)
Set targetWs = targetWb.Worksheets("勤怠")

'有給残数を取得（E30セルから）
nenkyuZan = targetWs.Range("E30").Value

'取得した値をマスターブックのB列に転記
masterWs.Cells(i, 2).Value = nenkyuZan

'対象ブックを閉じる（保存不要）
targetWb.Close SaveChanges:=False

GoTo NextRow

ErrorHandle:
'何らかのエラーが発生した場合
masterWs.Cells(i, 2).Value = "取得エラー"
If Not targetWb Is Nothing Then
targetWb.Close SaveChanges:=False
End If
On Error GoTo 0

NextRow:
Next i

'画面更新を元に戻す
Application.ScreenUpdating = True

MsgBox "有給残数の取得が完了しました。", vbInformation

End Sub

コードの流れを解説します。

lastRow = masterWs.Cells(Rows.Count, 1).End(xlUp).Row で、A列の職員名が入力されている最終行を自動取得しています。

職員が増えても減っても、コードを修正する必要がなくなります。

For i = 2 To lastRow のForループで、2行目から最終行まで1件ずつ処理を繰り返します。

fileName = folderPath & staffName & “_勤怠表.xlsx" では、職員名とフォルダパスを組み合わせてファイル名を自動生成。

職員名が「田中太郎」であれば C:\勤怠管理\田中太郎_勤怠表.xlsx というファイルパスが自動的に作られます。

If Dir(fileName) = “" Then でファイルの存在確認を行っています。

ファイルが見つからない場合は「ファイルなし」と転記してスキップするので、途中でエラーが起きて処理が止まることを防いでいます。

On Error GoTo ErrorHandle でエラーハンドリングを設定しています。

シート名が違う、ファイルが壊れているなど予期しないエラーが発生した場合でも「取得エラー」と表示して処理を続けるため、1件のエラーで全体の処理が止まることがありません。

処理速度を大幅にアップさせる3つのテクニック

職員数が多くなると、ブックを開いたり閉じたりする処理が積み重なって時間がかかってきます。

処理速度を速くするためのテクニックを3つ紹介します。

テクニック①：Application.ScreenUpdatingをFalseにする

先ほどのコードでも使用しましたが、Application.ScreenUpdating = False は処理速度改善に最も効果的な設定の1つです。

通常、VBAが実行されるたびにエクセルの画面が更新されますが、これをオフにすることで処理が一気に速くなります。

処理が終わったら必ず Application.ScreenUpdating = True で元に戻しましょう。

戻し忘れると、VBAの処理が終わったあともエクセルの画面更新がされなくなってしまいます。

テクニック②：Application.DisplayAlertsをFalseにする

別ブックを閉じるときなど、エクセルが確認ダイアログを表示しようとすることがあります。

Application.DisplayAlerts = False を追加しておくと、こういったダイアログが自動的に非表示になり、処理が止まりません。

Application.DisplayAlerts = False
'処理内容
Application.DisplayAlerts = True

こちらも処理後は必ずTrueに戻してください。

テクニック③：Calculationを手動に設定する

大量のデータを処理するとき、ブックを開くたびに計算式が再計算されて時間がかかることがあります。

Application.Calculation = xlCalculationManual '計算を手動に
'処理内容
Application.Calculation = xlCalculationAutomatic '計算を自動に戻す

この3つを組み合わせると、処理時間が劇的に短縮されます。

職員30人分のブックを処理する場合でも、数十秒以内に完了させられるはずです。

フォルダ内のすべての勤怠表ブックを自動処理するVBA

先ほどのコードでは、マスターブックの職員一覧を元に1件ずつ処理していました。

もう1つの方法として、フォルダ内にあるすべてのエクセルファイルを自動で処理するパターンも紹介します。

Sub フォルダ内全ブック処理()

Dim masterWs As Worksheet
Dim targetWb As Workbook
Dim folderPath As String
Dim fileName As String
Dim staffName As String
Dim nenkyuZan As Variant
Dim rowNum As Long

Set masterWs = ThisWorkbook.Worksheets("有給一覧")
folderPath = "C:\勤怠管理\"

'B列以降をクリア（2行目から）
masterWs.Range("A2:B1000").ClearContents

Application.ScreenUpdating = False

'書き込み開始行
rowNum = 2

'指定フォルダ内の最初のxlsxファイルを取得
fileName = Dir(folderPath & "*.xlsx")

'ファイルが見つかる間は繰り返す
Do While fileName <> ""

'処理中のファイルがマスターブック自身でなければ処理する
If fileName <> ThisWorkbook.Name Then

Set targetWb = Workbooks.Open(folderPath & fileName)

On Error Resume Next
Dim targetWs As Worksheet
Set targetWs = targetWb.Worksheets("勤怠")
On Error GoTo 0

If Not targetWs Is Nothing Then
'職員名（ファイル名から「_勤怠表.xlsx」を除いた部分）を取得
staffName = Replace(fileName, "_勤怠表.xlsx", "")
nenkyuZan = targetWs.Range("E30").Value

masterWs.Cells(rowNum, 1).Value = staffName
masterWs.Cells(rowNum, 2).Value = nenkyuZan
rowNum = rowNum + 1
End If

targetWb.Close SaveChanges:=False
Set targetWs = Nothing

End If

'次のファイルを取得
fileName = Dir()

Loop

Application.ScreenUpdating = True

MsgBox "全ブックの有給残数取得が完了しました。" & vbCrLf & _
"取得件数：" & (rowNum - 2) & "件", vbInformation

End Sub

コードの流れを解説します。

Dir(folderPath & “*.xlsx") で、指定フォルダ内の.xlsxファイルを1件ずつ取得します。

Do While fileName <> “" は「ファイル名が空でない間は繰り返す」という命令で、フォルダ内のすべてのxlsxファイルを処理し終えるまでループが続きます。

次のファイルへ進むには fileName = Dir() と書きます。

引数なしで Dir() を呼び出すと、前回と同じ条件で次のファイル名を返してくれます。

Replace(fileName, “_勤怠表.xlsx", “") で、ファイル名から「_勤怠表.xlsx」の文字列を除いて職員名だけを取り出しています。

このパターンを使えば、マスターブックに職員名を事前に入力しておく必要がなく、フォルダにファイルを追加するだけで自動的に処理対象に含まれます。

関連記事「別シートにテキストを自動転記」では、シートを指定するVBA、転記VBAが設定されたエクセルファイルをダウンロードすることができます。