【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する

photo credit: duncan 300 via photopin (license)

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

エクセルVBAでバラバラの経費精算書のデータをまとめるマクロの作り方をお伝えしています。

前回の記事はコチラ。

【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと

エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。今回は、エクセルVBAでマクロを作るときに知っておきたいマスタデータのこと、またその準備の仕方についてお伝えしていきます。

マスタデータとは何か、またその作り方についてお伝えしました。

さて、今回はそのマスタデータでデータを取り出すキーとなる社員Noをファイル名から取り出していきますよ。

ということで、エクセルVBAで開いたブックのファイル名から番号を取り出して数値に変換する方法をお伝えします。

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

これまでのおさらい
1. 手入力によるミスや揺らぎによる影響を避けるために
ファイル名から社員番号を切り出す
まとめ
連載目次：エクセルVBAで経費データをデータベースに集約する

これまでのおさらい

社員の皆さんから集めたエクセルでできた経費精算書のデータがあります。

これらを、同じフォルダに放り込んでおいてマクロを走らせれば、全ての経費データを自動で集めてくれるというマクロを作成しています。

ここまでで作成したコードはコチラです。

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

手入力によるミスや揺らぎによる影響を避けるために

ただ、以下の部分は各スタッフさんの手入力になるのですが、入力ミスや揺らぎが混入する可能性があるわけです。

それを防ぐために、以下のような「社員マスタ」を用意しました。

社員Noだけ指定してあげれば、部署No、部署、氏名は全部マスタから抽出できるわけです。

それで、社員Noはどこから抽出するかというと、以下の形式で構成されている、経費精算書ファイルのファイル名です。

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

末尾の4桁のXXXXが社員Noにあたりますので、これを抜き出し、かつ数値に変換する方法を紹介していきます。

ファイル名から社員番号を切り出す

さあ、上記の形式で構成されるファイル名から社員Noを抜き出していきましょう。

Replace関数で拡張子を取り除く

まず、ファイル名の末尾には拡張子「.xlsx」がありますのでこれを取り除きます。

特定の文字列を取り除きたい場合はReplace関数を使います。

Replace(対象とする文字列, 置換する文字列, 置換後の文字列)

取り除くというか、Replace関数のもともとの機能は”置換”なのですが、置換後の文字列を「””」と指定することで取り除くのと同様の働きをします。

今回のケースでは、以下のようにすればOKです。

Replace(f.Name, ".xlsx", "")

Right関数で末尾から文字を切り出す

拡張子が取り除ければ、以前使用したRight関数を使って末尾から文字を切り出して取得することができます。

つまり、以下のようにすれば、社員Noを取得することができます。

Right(Replace(f.Name, ".xlsx", ""), 4)

Val関数で文字列型を数値型に変換する

ただ、実はもう一工夫必要です。

というのも、実際の社員Noは「数値型」なのですが、上記ファイル名から取得した値は「文字列型」です。

型が異なるので、「数値型」に変換して揃える必要があります。

それで、文字列を数値に変換するときはVal関数を使います。

Val(文字列)

今回の場合ですが、社員Noを表す数値型の変数はstaffIdですから、以下のようにすることで社員Noを数値型で取得することができます。

Dim staffIdAs Long
staffId= Val(Right(Replace(f.Name, ".xlsx", ""), 4))

まとめ

以上、エクセルVBAで開いたブックのファイル名から番号を取り出して数値に変換する方法をお伝えしました。

Replace関数、Right関数、Val関数を使いました。

どれも使用頻度の高い関数ですので、ぜひ使いこなせるようになりたいですね。

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

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 = Val(Right(Replace(f.Name, ".xlsx", ""), 4))
            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

さて、次回ですがこの社員Noをキーにして、マスタから他のデータを引っ張ってきたいと思います。