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

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

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

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

前回の記事はコチラ。

エクセル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

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

あとは、以下の２つの処理を追加すれば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

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

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