フォルダに存在するExcelファイルを一括で参照処理する（Excelツール）

概要

ExcelとVBAを利用して、フォルダにある複数のExcelファイルの内容を参照して一覧するツールを作成してみましょう。
今回は、フォルダに格納している複数の請求書の内容を参照して請求情報を一覧してみます。

ツールの実行結果は、こんな感じ

例えば、会計処理を行う際に請求の一覧を作成するとき
沢山ある請求書ファイルをひとつひとつ開きながら転記していくのは
大変ですが、このツールがあれば一気に一覧してくれます。

※今回はこんな感じの請求書ファイルを入力しています。

交通費の清算書や勤務表など
同じ形式で出来たExcelファイルを一括で処理する場合
こんなツールを用意しておくだけで
かなりの時間の短縮をすることが出来ます。

Excelを使った文書を利用するなら
ほとんどの業務処理を自動化することが出来ますし
自動化する前提で業務を考えるべきです。

今回の記事は、以下の内容を応用しています。
フォルダに存在するファイルをExcelシートに一覧する（Excelツール）
ExcelとVBAを利用して、フォルダに存在するファイルを一覧するツールを作成してみましょう。フォルダのパスを指定して実行するとサブフォルダの内容まですべて別シートに一覧します。
design-pocket.com
2021.11.17

ツールの要件

指定したフォルダに存在するExcelファイル（今回は請求書ファイル）を一覧する。
（サブフォルダ内のファイルも対象とする。）

Input

フォルダの指定

・利用者は、menuシートに請求書を格納したフォルダ（のパス）を指定出来る。

（例：menuシート G4セルにフォルダパスを指定出来る仕様)

Output

一覧表示

・listシートに結果を一覧表示する
・表示項目は、請求日、請求書No、請求先、請求金額、ファイル名、フォルダのパス名

（例：listシート)

前提条件

読み込む請求書ファイルの仕様

・フォルダに保存するファイルは請求書ファイルのみとする。
・請求書ファイルのレイアウトはテンプレート通りとする。
　セル位置は、請求書番号：H1、請求日：H2、請求先：A6、請求金額：C14
（今回はサンプルのため、前提条件のチェックは行いません。）

ツール実行例

実行時の状態

フォルダのパスを指定した様子

（パス：C:\請求書を指定）

指定したフォルダの内容

（パス：C:\請求書の内容、配下をtreeコマンドで表示したもの）

treeファイルはコマンドの結果からわかる通り
請求書ファイルは以下の5ファイルになります。

C:\請求書\AAAAA社配下

・A202110.xlsx
・A202111.xlsx

C:\請求書\BBBBB社配下

・B202109.xlsx
・B202110.xlsx
・B202111.xlsx

※請求先はAAAAA社とBBBBB社
　請求書ファイル名は頭文字+年月で構成

5つの請求書ファイルのヘッダー内容
AAAAA社


BBBBB社

実行した結果

（前述した5ファイルが一覧された）

ツールの内容実装内容

本ツールは、Excel VBAで実装している。

コード

本ツールは、3つのSUBで構成している。
・メイン処理
・フォルダ処理
・ファイル処理
※コードをそのままコピーして実装する際は
ひとつのModuleに張り付けるようにしてください。
　（Private宣言を行っている変数があるため）

メイン処理

必要なオブジェクトの生成と参照やシート全体の処理を行っている。

'ブックオブジェクト（自分自身）
Private b As Workbook

'シートオブジェクト
Private s_menu As Worksheet
Private s_list As Worksheet

'指定したフォルダパスの保持域
Private spath As String

'一覧表示の際のラインカウンタ
Private line_ctr As Integer

'ヘッダ位置（行）
Const HEADPOS = 3

'
' メイン処理
'
Sub main()

 'このブックオブジェクトの参照を取得
  Set b = Workbooks("sample02.xlsm")

 'このブックのmenuシートオブジェクトの参照を取得
  Set s_menu = b.Worksheets("menu")
 'メニューシートからフォルダパスの値を取得して保持
  spath = s_menu.Cells(4, 7)
  
 'このブックのlistシートオブジェクトの参照を取得
  Set s_list = b.Worksheets("list")
  s_list.Activate
  
 '指定されたフォルダパスをlistシートに表示
  s_list.Cells(2, 1) = "（指定フォルダ：" & spath & "）"
  
  'listシートに以前の結果が残っていたらクリア
  lastrow = s_list.Cells(Rows.Count, 1).End(xlUp).Row
  If lastrow > HEADPOS Then
    temprange = "4:" & Trim(Str(lastrow))
    s_list.Range(temprange).Delete
  End If
  
  'ラインカウンタクリア
  line_ctr = HEADPOS
  FolderSearch spath
  
  '罫線
  Range(s_list.Cells(4, 1), s_list.Cells(line_ctr, 7)).Borders.LineStyle = xlContinuous
  
  MsgBox "おわり"
  
End Sub
説明

全体的な説明はコメントを参照
1-15行め：モジュールのスコープで変数と定数の宣言指定をしています。
　　　　　（ここで宣言したものはメイン処理、フォルダ処理の双方から参照可能）
22-32行め：ブックオブジェクトやシートオブジェクトの参照
34-35行め：フォルダパスの取得
37-42行め：listシートの初期化（前回の結果をクリア）
44-46行め：フォルダ処理の実行
49-52行め：listシートの罫線加工と終了メッセージ

フォルダの処理

サブフォルダの中まで一覧したいため、再帰検索しながら処理をすすめる。

'
' フォルダ内の再帰検索処理
'
Private Sub FolderSearch(foldername As String)

  Dim fso As Object
  Dim folder As Object
  Dim subfolder As Object
  Dim file As Object
  
  'FileSystemObjectオブジェクト作成
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set folder = fso.GetFolder(foldername)
  
  'サブフォルダの再帰処理
  For Each subfolder In folder.SubFolders
    FolderSearch subfolder.Path
  Next subfolder
  
  'フォルダ内に存在するファイル毎の処理
  For Each file In folder.Files
    With file
      'ラインカウンタアップ
      line_ctr = line_ctr + 1
      '1行分編集
      s_list.Cells(line_ctr, 1) = line_ctr - HEADPOS
      s_list.Cells(line_ctr, 6) = file.Name
      s_list.Cells(line_ctr, 7) = foldername
      
      '請求書の処理
      Dim tblx(4)
      getBillData file.Path, tblx()
      
      s_list.Cells(line_ctr, 2) = tblx(2)
      s_list.Cells(line_ctr, 3) = tblx(1)
      s_list.Cells(line_ctr, 4) = tblx(3)
      s_list.Cells(line_ctr, 5) = tblx(4)
      
    End With
  Next file

End Sub
説明

全体的な説明はコメントを参照
4行め：サブの入り口
　　　　フォルダーのパスを受け取ってlistシートに請求書の情報を一覧する
6-9行め：Scripting.FileSystemObjectのオブジェクト用
11-13行め：Scripting.FileSystemObjectのオブジェクト作成
15-18行め：処理中のフォルダに存在するサブフォルダの処理
　　　　　（自分で自分を呼ぶ、再帰処理）
20-40行め：処理中のフォルダに存在する請求書の情報を
　　　　　listシートに書き出す。
　　　　　請求書の情報取得は、ファイル処理を呼び出して処理

ファイルの処理

フォルダ内に存在する請求書ファイルを開いて内容を参照して
呼び出し側に戻す

'
' 請求書ファイルを開き請求書情報を取得して返す
'
Private Sub getBillData(spath, tblx())

  '与えられた引数をパス情報として請求書ファイルを開く
  Dim wb As Workbook
  Workbooks.Open spath, ReadOnly = True
  Set wb = ActiveWorkbook

  '請求書情報を取得
  Dim wp As Worksheet
  Set wp = wb.Worksheets("bill")
  
  tblx(1) = wp.Cells(1, 8)
  tblx(2) = wp.Cells(2, 8)
  tblx(3) = wp.Cells(6, 1)
  tblx(4) = wp.Cells(14, 3)

  wb.Close
  
  Set wb = Nothing

End Sub
説明

全体的な説明はコメントを参照
4行め：サブの入り口
　　　　ファイルのパス（spath）を受け取る
　　　　処理後、結果格納用のテーブル（tblx()）に情報を設定し戻る
7-9行め：請求書ファイルを開き、ブックオブジェクトとして保持
12-13行め：請求書のシートを保持
　　　　　（前提としてbillシートが請求書の内容とする）
15-18行め：請求書のヘッダーから情報を取得し結果格納用テーブルに設定
　　　　　・請求日、請求書No、請求先、請求金額
20-22行め：開いている請求書ファイルを閉じる

補足

このツールは、メイン処理、フォルダ処理、ファイル処理で構成されています。
Excelファイルを一括で処理するツールを作成する場合は
同じような構造で処理を記述することが出来ます。

次回は、このツールを拡張して
フォルダ内に存在するすべてのExcelブックを更新するツールを
掲載する予定です。

タイトルとURLをコピーしました