エクセルVBAで請求書作成マクロをテーブルを活用して作る

invoice

photo credit: Andrew Stawarz Fenman’s Cottage via photopin (license)

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

エクセルVBAで請求書作成マクロのテーブルを活用したバージョンを作成しています。

前回の記事はコチラ。

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

請求書マクロの中枢部ともいっていい、請求データを該当の取引先用のブックのテーブルに転記をしていく処理を作成しました。

今回はいよいよ仕上げをして完成をさせます。

ということで、エクセルVBAで請求書作成マクロをテーブルを活用して作成する方法です。

行ってみましょう!

スポンサーリンク

前回のおさらい

まずは、目指すべきマクロのゴールについてお伝えします。

ブック「請求データ.xlsm」には、まず以下の「データ」シート(オブジェクト名は「wsData」)があります。

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

「取引先マスタ」テーブルの分だけ、請求書を作る必要があり、その請求書には**「請求データ」テーブルの該当のデータを転記していきます。

そして、その取引先ごとに作る請求書のひな形となるのが以下の「ひな形」シート(オブジェクト名は「wsTemplate」)です。

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

それで、前回までに作成したコードがコチラです。

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
    
    Dim wb As Workbook: Set wb = ActiveWorkbook
    With ActiveSheet
        .Name = client
        Dim tbTemplate As ListObject: Set tbTemplate = .ListObjects("ひな形データ")
        
        Dim i As Long
        For i = 1 To tbData.ListRows.Count
            Dim rowData As ListRow: Set rowData = tbData.ListRows(i)
            If rowData.Range(2).Value = client Then
                With tbTemplate.ListRows.Add
                    .Range(1).Value = rowData.Range(3).Value
                    .Range(2).Value = rowData.Range(4).Value
                    .Range(3).Value = rowData.Range(5).Value
                End With
            End If
        Next i

        '3. 請求金額、請求先の社名や住所などの情報を入力する
    End With
    '4. ファイル名を決めて保存する

End Sub

取引先ごとにブックを作り、ひな形シートをコピーし、該当の請求データを転記するまでの処理はできています。

あとは、以下の2つの処理を追加すればOKですね。

  • 3.請求金額、請求先の社名や住所などの情報を入力する
  • 4.ファイル名を決めて保存する

請求書に必要な情報を転記する

まずは「 3.請求金額、請求先の社名や住所などの情報を入力する」ですが、以下のようなコードを作りました。

Dim total As Long: total = .Range("D21").Value + .Range("D22").Value
.Range("A19").Value = "ご請求金額: " & Format(total, "#,##0") & " 円"

.Range("A3").Value = client & " 御中"
.Range("A5").Value = rowClient.Range(2).Value
.Range("A6").Value = rowClient.Range(3).Value
.Range("A7").Value = rowClient.Range(4).Value

Valueプロパティで取り出した値を、該当のセルのRangeオブジェクトに書き込んでいくだけですので、複雑な処理ではありません。

2行目のFormat関数については、以下の記事もご参考ください。

【初心者向けエクセルVBA】文字列の連結&Format関数での書式変更
エクセルVBAで文字列を連結する、文字列の書式を変更するなどの「文字列」に関するいくつかのテクニックを紹介しつつ、データ一覧から請求書を自動で作成するというシリーズ連載の序盤の完成を目指します。

ファイル名を決めて保存する

続いて、「4.ファイル名を決めて保存する」の処理です。

コチラです。

Dim fileName As String
fileName = ThisWorkbook.Path & "\" & Format(Date, "yyyymm") & "請求書_" & client & ".xlsx"
wb.SaveAs fileName
wb.Close

ファイル名を生成して、その名前で保存をして閉じるというものですね。

保存先は、現在マクロを書いているブック「請求データ.xlsm」と同じフォルダになります。

このあたりの処理については以下の記事で詳しく紹介しています。

【初心者向けエクセルVBA】ワークブックを別名で保存して閉じる方法
初心者向けエクセル VBAのシリーズとして、請求書を作成するマクロの作り方についてお伝えしています。今回は、ワークブックをSaveAsメソッドで別名で保存し、Closeメソッドで閉じる方法をお伝えします。

テーブルを使った請求書マクロ

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

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
    
    Dim wb As Workbook: Set wb = ActiveWorkbook
    With ActiveSheet
        .Name = client
        Dim tbTemplate As ListObject: Set tbTemplate = .ListObjects("ひな形データ")
        
        Dim i As Long
        For i = 1 To tbData.ListRows.Count
            Dim rowData As ListRow: Set rowData = tbData.ListRows(i)
            If rowData.Range(2).Value = client Then
                With tbTemplate.ListRows.Add
                    .Range(1).Value = rowData.Range(3).Value
                    .Range(2).Value = rowData.Range(4).Value
                    .Range(3).Value = rowData.Range(5).Value
                End With
            End If
        Next i

        Dim total As Long: total = .Range("D21").Value + .Range("D22").Value
        .Range("A19").Value = "ご請求金額: " & Format(total, "#,##0") & " 円"
        
        .Range("A3").Value = client & " 御中"
        .Range("A5").Value = rowClient.Range(2).Value
        .Range("A6").Value = rowClient.Range(3).Value
        .Range("A7").Value = rowClient.Range(4).Value

    End With
     
    Dim fileName As String
    fileName = ThisWorkbook.Path & "\" & Format(Date, "yyyymm") & "請求書_" & client & ".xlsx"
    wb.SaveAs fileName
    wb.Close
    
Next rowClient

End Sub

実行をすると、今回は新規作成されたブックは開きっぱなしにはならずに、一度閉じます。

以下のように、マクロを記述しているブックと同じフォルダに保管されます。

取引先ごとに作成したブックを保存したフォルダ

そして、それぞれを開くと以下のような内容になっています。

取引先ごとに作成した請求書ブックを開く

まとめ

以上、エクセルVBAでテーブルを使った請求書作成マクロについて紹介をしました。

今回、新たに追加した処理はテーブルかどうかに関わらずで活用できるものでしたね。

さて、全体を見渡すと、少しコードは長くなりましたが、テーブルを活用しているおかげでシート数も少なくて済みますし、可読性は比較的高いのではないかと思います。

全く同じ機能ではありませんが、以下の記事の完成コードと比較してみてください。

【初心者向けエクセルVBA】取引先別に請求書を作成するマクロを作る
初心者向けのエクセル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をコピーしました