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

id-number

photo credit: duncan 300 via photopin (license)

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

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

前回の記事はコチラ。

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

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

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

ということで、エクセル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をキーにして、マスタから他のデータを引っ張ってきたいと思います。

【エクセルVBA入門】マクロでVLookupメソッドを使ってデータを検索する方法
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズの7回目です。今回は、業務で有効なテクニックとしてもう一つ、マスタシートからVLookupメソッドでデータを取得してくる方法についてお伝えしていきます。

どうぞお楽しみに!

連載目次:エクセルVBAで経費データをデータベースに集約する

請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。
  1. 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
  2. 【エクセルVBA入門】Do While~Loop文で条件を満たす間繰り返し
  3. 【エクセルVBA入門】繰り返しを使ってデータの転記をするときの2つのポイント
  4. 【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
  5. 【エクセルVBA入門】他のワークブックをWithで開く&保存せずに閉じる
  6. 【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
  7. 【エクセルVBA入門】For Each~Next文でフォルダ内のブック全てを開く方法
  8. 【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
  9. 【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
  10. 【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと
  11. 【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
  12. 【エクセルVBA入門】マクロでVlookupを使ってデータを検索する方法
  13. 【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
  14. 【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
  15. 【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する
タイトルとURLをコピーしました