【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法

photo credit: Nolan Reimold, Matt Angle via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。

前回の記事はコチラ。

【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説

エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。今回は、実行のたびにデータをシートに追加できるようにします。また、最終行番号を求めるステートメントについて徹底的に解説をしていきます。

最終行数を求め、経費データを追加していけるように修正をしました。

さて、実際に業務に使う場合、他のユーザーが間違えて想定しない入力をしてしまったりすることがあります。

そのような問題を先回りして備えることで、業務全体の精度や効率を上げていくことができるようになりますよね。

今回から、そのいくつかのテクニックについてお伝えしていきますよ。

まずは、「ファイル名」を活用してみたいと思います。

ということで、エクセルVBAで開いたブックのファイル名から指定した文字数の文字列を抜き出す方法です。

では、行ってみましょう！

前回のおさらい
人が入力することによる人為的なミスは必ず起こる
人為的な入力ミスを事前に回避もしくは無効化する方法
ファイル名に命名規則を与える
1. ファイル名から文字を切り出し対象月を生成
ファイル名の年月を利用した経費データ収集プロシージャ
まとめ
連載目次：エクセルVBAで経費データをデータベースに集約する

前回のおさらい

まずおさらいからです。

マクロを記述しているブックには以下のようなデータを集めるための「経費データ」というシートがあります。

各スタッフがそれぞれ作成した経費精算書が、同じフォルダの「data」というフォルダ配下にゴソっと格納されていて、その経費データを収集しようというものです。

例として太郎さんの経費精算書はこんな感じです。

それで、フォルダ内のファイルすべてについて開いて、データを収集するプログラムがコチラです。

Sub 経費精算データ取り込み()

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim di As Long: di = wsData.Cells(Rows.Count, 1).End(xlUp).Row + 1
Dim f As File
For Each f In fso.GetFolder(ThisWorkbook.Path & "\data").Files
    With Workbooks.Open(f.Path)
        With .Worksheets(1)
        
            Dim month As Date: month = .Range("G4").Value
            Dim departmentName As String: departmentName = .Range("G6").Value
            Dim staffId As Long: staffId = .Range("G8").Value
            Dim staffName As String: staffName = .Range("G7").Value
            
            Dim i As Long: i = 12
            Do While .Cells(i, 1).Value <> ""
                wsData.Cells(di, 1).Value = month '1 対象月
                wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
                wsData.Cells(di, 3).Value = ""  '3 部署No
                wsData.Cells(di, 4).Value = departmentName '4 部署
                wsData.Cells(di, 5).Value = staffId '5 社員No
                wsData.Cells(di, 6).Value = staffName '6 氏名
                wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
                wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
                wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
                wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
                i = i + 1: di = di + 1
            Loop
        End With
        .Close
    End With
Next f

End Sub

前回仕込んだ最終行番号を求めるステートメントのおかげで、毎月実行するとデータがどんどん追加で蓄積されるようになりまいた。

人が入力することによる人為的なミスは必ず起こる

さて、では新しい月になり新たに集まってきた経費精算書について、このプログラムを使ってデータ追加をしてみましょう。

データに入力ミスが…ありますね。

まず、対象月ですが本来は8月とすべきところを、先月のファイルを使いまわしたのでしょう、7月のままになっています。

また、氏名の箇所ですが、苗字と名前の間のスペースが先月はあったのに、今回は入れ忘れてしまっています。これはエクセルから見ると、同一人物という判断にはならず、フィルタでも関数でも集計の邪魔になってしまいます。

上記のようなミスはよくあるパターンですが、皆さんはどのように対処をされますか？

担当者にミスを指摘して「どうしてあなたはいつも間違えるのですか？次こそは絶対に間違えないようにしなさい」などと、口酸っぱく言い続けますか？

しかし、残念ながらそれでは解決は難しいはずです。

人間はコンピュータと違い、必ずどこかでうっかりミスをします。100％とか絶対ということはありません。

うっかりミスに対して怒るだけ無駄です。

それよりも、業務フローを組み立てる際は、このうっかりが必ず存在するということを前提に組み立ててしまうのが有効です。

人為的な入力ミスを事前に回避もしくは無効化する方法

では、この件について具体的に業務フローで解決をしてしまいましょう。

経費精算書のフォーマットを再度確認してみましょう。

この赤囲みの部分ですが、手入力なので、それをそのまま使うと入力ミスが混在する可能性があります。

ただ、その全てについて、その入力ミスをほぼ事前に発見したり、無効化したりができるようになります。

まず、その1つ目の手法がファイル名を使うことです。

ファイル名に命名規則を与える

経費精算書を提出してもらうときのファイル名の命名ルールを

YYYYMM経費精算書_名前_XXXX.xlsx

としてもらいます。

重要なポイントはファイル名の頭から6桁の年月（YYYY：西暦で年を表す＋MM：2桁で月を表す）と、末尾の4桁の社員番号です。

例えば、太郎さんの社員番号が3001であれば、その8月分の経費精算書のファイル名は

201808経費精算書_経費_3001.xlsx

となります。

これで、ミスを減らすことができるロジックは以下のとおりです。

まず、経費精算書の場合は、前月のファイルをコピーして使う場合が多いと思います。

社員番号はいじる必要がないので、年月だけ修正をする形になるはずです。

むしろ、年月を変えないと複製ができない（同じファイル名では保存できませんから）ので、おそらく変更をすることになるでしょう。

また、受け取った側も年月のミスは見つけやすいはずです。

というのも、フォルダにまとめた時点で、最初の6桁がパッと見で揃っていないと、おかしいですからね。

ファイル名から文字を切り出し対象月を生成

For Each～Nextルーチン内で使用しているファイル名はf.Nameで取得できますから、このファイル名を加工していきます。

Fileオブジェクト.Name

特定の文字列の最初もしくは最後から指定して文字数を抜き出す場合は、Left関数とRight関数を使います。

Left(文字列,文字数)

Right(文字列,文字数)

では、これらを使って、まずはファイル名から年月を抜き出します。

年月はファイル名の最初の6文字ですから、文字列型の変数periodを使って以下のようにすればOKです。

Dim period As String: period = Left(f.Name, 6)

次に、この6文字をさらに年と月に分けて、日付形式にします。

年、月、日を指定して日付を生成したい場合はDateSerial関数を使って

Dim month As Date: month = DateSerial(Left(period, 4), Right(period, 2), 1)

とします。日は今回は1で固定です。DateSerialの使い方についてはこちらを参考ください。

【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する

初心者向けエクセルVBAのシリーズとして請求データから請求書を作るマクロの作り方についてお伝えしています。今回はDateSerial関数を使って月末日または翌月末日を自動算出する方法についてお伝えします。

ファイル名の年月を利用した経費データ収集プロシージャ

まとめのコードはコチラです。

Sub 経費精算データ取り込み()

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim di As Long: di = wsData.Cells(Rows.Count, 1).End(xlUp).Row + 1
Dim f As File
For Each f In fso.GetFolder(ThisWorkbook.Path & "\data").Files
    With Workbooks.Open(f.Path)
        With .Worksheets(1)
            
            Dim period As String: period = Left(f.Name, 6)
            Dim month As Date: month = DateSerial(Left(period, 4), Right(period, 2), 1)
            Dim departmentName As String: departmentName = .Range("G6").Value
            Dim staffId As Long: staffId = .Range("G8").Value
            Dim staffName As String: staffName = .Range("G7").Value
            
            Dim i As Long: i = 12
            Do While .Cells(i, 1).Value <> ""
                wsData.Cells(di, 1).Value = month '1 対象月
                wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
                wsData.Cells(di, 3).Value = ""  '3 部署No
                wsData.Cells(di, 4).Value = departmentName '4 部署
                wsData.Cells(di, 5).Value = staffId '5 社員No
                wsData.Cells(di, 6).Value = staffName '6 氏名
                wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
                wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
                wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
                wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
                i = i + 1: di = di + 1
            Loop
        End With
        .Close
    End With
Next f

End Sub

実行結果はコチラ。入力ミスがあってもファイル名から正しい対象月を拾うことができています。

030