エクセルVBAでテーブルを走査して取引先ごとのブックを作るマクロ

tables

photo credit: Spiros Vathis just a kiss via photopin (license)

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

請求書の作成マクロを題材にエクセルVBAでテーブルを操作する方法をお伝えしています。

前回の記事はコチラ。

エクセルVBAでテーブルのデータ行を追加して値を入力をする方法
請求書作成マクロを題材としてエクセルVBAでテーブルを操作する方法についてお伝えするシリーズです。今回は、テーブルのデータ行を追加して値を入力をする、Addメソッドの使い方をお伝えしていきますね。

テーブルに行を追加してデータを入力する方法をお伝えしました。

さて、テーブルの操作方法はだいぶ出揃ったので、今回からそれらをいかして、実際に請求書マクロを作成していきます。

まずは、エクセルVBAでテーブルを走査して取引先ごとのブックを作るところまでを作っていきます。

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

スポンサーリンク

請求書マクロの仕組み

まず、目指す請求書作成マクロの仕組みについて解説をしていきます。

「請求データ.xlsm」というエクセルブックに2つのシートが含まれています。

一つは以下の「データ」シートで、そのオブジェクト名は「wsData」としています。

請求書作成マクロのデータシート

このシートには以下2つのテーブルが作られています。

  • 請求データ」テーブル:請求すべきデータを一覧しているテーブル
  • 取引先マスタ」テーブル:取引のある取引先リスト

なお、2つのテーブルは上記のテーブル名に変更済みです。

もう一つのシートが以下「ひな形」シートで、オブジェクト名は「wsTemplate」です。

請求書作成マクロのひな形シート

こちらのシートは、取引先ごとに作成する請求書のひな形になるシートです。

テーブルは「ひな形データ」というテーブル名で、データ行は含まれていないものです。

請求書マクロの流れ

請求書マクロの流れは、以下のような流れになります。

  1. 「取引先マスタ」テーブルの取引先の数だけ繰り返す
    1. 「ひな形」シートをコピーしてブックを作成
    2. 「請求データ」テーブルの行を走査する
      • その行の取引先が、該当の取引先であれば
        • 作成したブックの「ひな形データ」に行を追加
        • 追加した行に「請求データ」の該当データを転記する
    3. 請求金額、請求先の社名や住所などの情報を入力する
    4. ファイル名を決めて保存する

この流れでマクロを作成していきます。

テーブルを走査して取引先ごとにブックを作る

まずは大枠の分のマクロを作成してみました。

コチラです。

Sub 請求書作成()

Dim tbData As ListObject: Set tbData = wsData.ListObjects("請求データ")
Dim tbClient As ListObject: Set tbClient = wsData.ListObjects("取引先マスタ")
Dim rowClient As ListRow

For Each rowClient In tbClient.ListRows
    Dim client As String: client = rowClient.Range(1).Value
    wsTemplate.Copy
    With ActiveSheet
        .Name = client
        Dim tbTemplate As ListObject: Set tbTemplate = .ListObjects("ひな形データ")
        '2. 「請求データ」テーブルの行を走査して転記処理などをする
        '3. 請求金額、請求先の社名や住所などの情報を入力する
    End With
    '4. ファイル名を決めて保存する
Next rowClient

End Sub

解説をしていきます。

ListObjectオブジェクトとしてテーブルを取得する

3,4行目は、各テーブルをListObjectオブジェクトとして取得する箇所です。

「請求データ」テーブルはtbDataとして、「取引先マスタ」テーブルはtbClientとして取り扱うことができるようになります。

WorksheetオブジェクトのListObjectsコレクションの取得についてはコチラ

エクセルVBAでテーブル操作~そのメリットと変換方法&ListObjectの取得
エクセルVBAでテーブルを操作する方法について丁寧に解説していきます。今回は、テーブルとは、エクセルのデータリストをテーブルに変換する方法、VBAでテーブルをオブジェクトとして取得する方法をお伝えします。

ListObjectsコレクションからテーブル名でテーブルを取得する方法はコチラですね。

エクセルVBAでテーブル名を使用してテーブルを取得する方法
請求書マクロの作成を題材として、エクセルVBAでテーブルを操作する方法についてお伝えしています。今回は、テーブル名の変更する方法と、テーブル名を使用してテーブルを取得する方法について解説をしていきます。

For Each文でテーブルの全ての行について繰り返す

7~15行目はFor Each文で「取引先マスタ」テーブルの全てのデータ行について繰り返す処理です。

どちらの取引先から処理をすべきか…といった順番は関係がないので、For Each文を使っちゃってます。

繰り返しのたびに、各データ行がrowClientという変数に格納されて、それをループ内で活用することができます。

データ行を表すオブジェクトはListRowオブジェクトです。

ListRowオブジェクトとそのループについては、以下の記事をご覧ください。

エクセルVBAでテーブルのデータ行についてループ処理を行う方法
請求書マクロを題材にエクセルVBAでテーブル操作する方法をお伝えしています。今回は、ListRowsオブジェクトがポイント。テーブルのデータ行についてループ処理を行う方法についてお伝えしていきます。

ListRowオブジェクトの特定の列のセルの値を取得する

8行目は、現在対象となっているデータ行の特定の列のセルの値を取得しています。

Rangeオブジェクトですが、インデックスが1なので、そのテーブルの

  • 現在の対象となっている行=rowClientの
  • 1列目=つまり「取引先名」のセル

のセルを表すRangeオブジェクトです。

Valueプロパティでその値を取得しているので、該当行の取引先名が取得できます。

このあたりは以下記事で紹介しています。

エクセルVBAでテーブルのデータ行を追加して値を入力をする方法
請求書作成マクロを題材としてエクセルVBAでテーブルを操作する方法についてお伝えするシリーズです。今回は、テーブルのデータ行を追加して値を入力をする、Addメソッドの使い方をお伝えしていきますね。

シートのコピーを新規ブックに作成する

そして、9行目は、ひな形シートを新規ブックにコピーする命令です。

WorksheetオブジェクトのCopyメソッドは、引数を与えない場合は、その対象は新規ブックになります。

また、11行目では、そのコピーしたシートのシート名を取引先名に変更をしています。

エクセルVBAは、作成されたブックやシートが直後にActiveになりますから、その特性を利用しています。

実行結果

では、上記プロシージャの実行結果をみてみましょう。

以下のように、取引先の分だけブックが作成されましたね。

取引先ごとにブックを作成するマクロの結果

まとめ

以上、エクセルVBAでテーブルを走査して取引先ごとのブックを作成するマクロを紹介しました。

今回は大枠の処理を作りましたが、次回はその中身の作成を進めていきます。

エクセルVBAでテーブル上の請求データを新規ブックに転記するマクロの作り方
エクセルVBAでテーブル機能を活用した請求書作成マクロの作成方法についてお伝えしてます。今回は、テーブル上の請求データを走査して、取引先ごとの新規ブックに転記するマクロの作り方をお伝えしていきます。

どうぞお楽しみに!

連載目次:エクセルVBAでテーブルを活用した請求書マクロを作る

エクセルのとっても便利な機能「テーブル」。VBAで操作するときも、テーブルならではの便利さを味わうことができます。請求書マクロを題材にVBAによるテーブルの操作方法をお伝えしていきます。
  1. エクセルVBAでテーブル操作~そのメリットと変換方法&ListObjectの取得
  2. エクセルVBAでテーブル名を使用してテーブルを取得する方法
  3. エクセルVBAでテーブルの見出し行・データ行・集計行の範囲の取得をする方法
  4. エクセルVBAでテーブルのデータ行についてループ処理を行う方法
  5. エクセルVBAでテーブルのデータ行を追加して値を入力をする方法
  6. エクセルVBAでテーブルを走査して取引先ごとのブックを作るマクロ
  7. エクセルVBAでテーブル上の請求データを新規ブックに転記するマクロの作り方
  8. エクセルVBAで請求書作成マクロをテーブルを活用して作る
  9. エクセルVBAでテーブルと列挙体を組み合わせると最強のコードが書ける理由
タイトルとURLをコピーしました