🗂

Excelマクロで仕事効率化（初心者向け）(3) | 一括請求書PDF作成

2024/07/19に公開

Excel

はじめに

前回は、請求書を作成するためのファンデーション（基礎）について説明をいたしました。これにより、VBAプログラミングの直前まで進めることができます。

今回は、実際にVBAプログラミングを実施して、一括での請求書PDFファイル作成を目指します。
ではスタートです。

データ入力

まずは、請求書に表示させるためのデータを入力します。
以下のような感じで入力してみました。
よく考えると、「金額」は「単価」と「数量」の掛け算で求められ、Excel計算式で計算をさせるため、このシートへの入力はやめました。ですのでL列やR列は空白にしています。

下に続く・・・

これで、データ入力は完了しました。

請求書作成プログラム

まずはプログラムを掲載します。

Public Sub DataOutput()
    Dim iRow As Long
    Dim iCol As Long
    
    ' データクリア
    Call DataClear
    
    For iRow = 2 To 1000
    
        ' 空白チェック
        If Sheet1.Cells(iRow, 1) = "" Then
            Exit For
        End If
    
        ' 請求先名
        Sheet2.Cells(2, 1) = Sheet1.Cells(iRow, 1) & " 御中"
        ' 請求No
        Sheet2.Cells(2, 8) = Sheet1.Cells(iRow, 2)
        ' 請求日
        Sheet2.Cells(3, 8) = Sheet1.Cells(iRow, 3)
        ' 件名
        Sheet2.Cells(6, 2) = Sheet1.Cells(iRow, 4)
        ' 支払期限
        Sheet2.Cells(7, 2) = Sheet1.Cells(iRow, 5)
        ' 振込先
        Sheet2.Cells(8, 2) = Sheet1.Cells(iRow, 6)

        For iCol = 1 To 5
        
            ' 摘要
            Sheet2.Cells(14 + iCol, 1) = Sheet1.Cells(iRow, (iCol - 1) * 6 + 7)
            ' 数量
            Sheet2.Cells(14 + iCol, 4) = Sheet1.Cells(iRow, (iCol - 1) * 6 + 8)
            ' 単位
            Sheet2.Cells(14 + iCol, 5) = Sheet1.Cells(iRow, (iCol - 1) * 6 + 9)
            ' 単価
            Sheet2.Cells(14 + iCol, 6) = Sheet1.Cells(iRow, (iCol - 1) * 6 + 10)
            ' 税率
            Sheet2.Cells(14 + iCol, 7) = Sheet1.Cells(iRow, (iCol - 1) * 6 + 11)
            ' 金額
            'Sheet2.Cells(14 + iCol, 8) = Sheet1.Cells(iRow, (iCol - 1) * 6 + 12)
    
        Next iCol
        
        Call OutputPDF(Sheet2.Cells(6, 2))
        
    Next iRow

End Sub

これから、各コード内容について説明します。

Public Sub DataOutput()

まずは、関数の宣言です。関数宣言ではSubを記述し、その後に関数名（ここではDataOutput()）を記述します。「( )」は、関数の入り口で使用するものであり、通常「引数」を入れることができます。関数の呼出し側で渡した引数があれば、その関数でも引数の情報を取得することができます。今回はその「引数」はないので「( )」カッコだけとします。
またPublicとありますが、これはそのモジュール以外からも関数呼び出しができることを示しています。これと対照になるのがPrivateです。これはそのモジュール内(ここではModule1）でしか呼び出すことができないことを示しています。
これが何の得になるかというと、関数を何でもかんでもPublicにして、他のモジュールからでも利用できるようにすると、モジュール間の依存関係が入り組んでしまい、プログラム改修などで大変な思いをするからです。例えて言うと「脳外科手術で腫瘍を取り出そうとしたが、多くの血管が絡み合ってオペが難航する」といった具合でしょうか。（ちょっと例えが悪かったか・・・）

    Dim iRow As Long
    Dim iCol As Long

次に変数の宣言です。変数を宣言することで変数に数字や文字を格納したり参照したりすることができます。今回の個所では、iRowとiColという変数を使うことができます。ちなみにiRowは「行(Row)」を表す変数であり、iColは「列(Column)」を表す変数です。

    ' データクリア
    Call DataClear

次に請求書シートのデータをクリア（空白）にする関数を呼びます。Callは関数を呼び出す宣言文であり、私が今回作成したDataClearはクリア処理をする関数です。DataClearについては後述します。

    For iRow = 2 To 1000

これはForループを示しており、iRowという変数が2から1000までの間ループすることを意味しています。
この「2」は「データ」シートの開始行が2であることを示し（1行目は項目のタイトル）、1000行目まで繰り返すことを示します。

        ' 空白チェック
        If Sheet1.Cells(iRow, 1) = "" Then
            Exit For
        End If

ここでは「セルの空白チェック」をするのですが、第1列目を軸として行下方（2行目、3行目・・・）に進めていき、セルの空白が発見されたらこのループは終了という事を示しています。
Sheet1は「データ」シートを示しています。

        ' 請求先名
        Sheet2.Cells(2, 1) = Sheet1.Cells(iRow, 1) & " 御中"
        ' 請求No
        Sheet2.Cells(2, 8) = Sheet1.Cells(iRow, 2)
        ' 請求日
        Sheet2.Cells(3, 8) = Sheet1.Cells(iRow, 3)
        ' 件名
        Sheet2.Cells(6, 2) = Sheet1.Cells(iRow, 4)
        ' 支払期限
        Sheet2.Cells(7, 2) = Sheet1.Cells(iRow, 5)
        ' 振込先
        Sheet2.Cells(8, 2) = Sheet1.Cells(iRow, 6)

「データ」シート(Sheet1)の情報を「請求書」シート(Sheet2)に転記しています。
「請求先名」については、「データ」シートのiRow行目・1列目のセルのデータと「御中」を付けたものを「請求書」シートの2行目・1列目のセルに表示させています。

「請求書No」については、「データ」シートのiRow行目・2列目のセルのデータを「請求書」シートの2行目・8列目のセルに表示させています。

これと同じ要領で、「請求日」「件名」「支払期日」「振込先」についても「請求書」シートに表示させます。

        For iCol = 1 To 5

iRowのループの中に、更にiColのループが入ることになります。
「データ」シートでは、「摘要」～「金額」を5回横方向に繰り返しており、「請求書」シート下部の「摘要表」にその繰り返しを反映させるため、iColが1から5までのループを設定しています。

            ' 摘要
            Sheet2.Cells(14 + iCol, 1) = Sheet1.Cells(iRow, (iCol - 1) * 6 + 7)
            ' 数量
            Sheet2.Cells(14 + iCol, 4) = Sheet1.Cells(iRow, (iCol - 1) * 6 + 8)
            ' 単位
            Sheet2.Cells(14 + iCol, 5) = Sheet1.Cells(iRow, (iCol - 1) * 6 + 9)
            ' 単価
            Sheet2.Cells(14 + iCol, 6) = Sheet1.Cells(iRow, (iCol - 1) * 6 + 10)
            ' 税率
            Sheet2.Cells(14 + iCol, 7) = Sheet1.Cells(iRow, (iCol - 1) * 6 + 11)
            ' 金額
            'Sheet2.Cells(14 + iCol, 8) = Sheet1.Cells(iRow, (iCol - 1) * 6 + 12)
    
        Next iCol

「摘要」の箇所を例に取ると、「データ」シート(Sheet1)のiRow行・(iCol - 1) * 6 + 7列目のセルのデータを、「請求書」シート(Sheet2')の14 + iCol'行目・1列目のセルに転記する処理です。
iRowは、「データ」シートの処理該当(カーソル)行を示しており、右辺では「Sheet1.Cells(iRow, ...)」と記述しています。
また、「データ」シートの「摘要」～「金額」の部分は、「摘要」の始まりが7列目であり、12列目で1セットとなっており、その間隔は6であるため「(iCol - 1) * 6 + 7」という表現となります。
同じ要領で、「数量」「単位」「単価」「税率」「金額」についても記載していきます。
ただ、「金額」については以前にも説明しましたが、空白（ブランク）にしているため、コメントアウトしています。

        Call OutputPDF(Sheet2.Cells(6, 2))

次に請求書シートをPDFファイルに保存する関数を呼びます。私が今回作成したOutputPDFはPDFファイルに名前を付けて保存する関数です。引数をSheet2.cells(6,2)としており、件名を「引数」としてOutputPDFに渡しています。OutputPDFについては後述します。
必要に応じて、「請求番号」や「会社名」もしくはそれらを繋げたファイル名に変更しても良いかと思います。

請求書シートクリアプログラム

「請求書」シートのセルの情報をクリアする処理関数を示します。

Private Sub DataClear()
    Dim iRow As Long

    ' 請求先名
    Sheet2.Cells(2, 1) = ""
    ' 請求No
    Sheet2.Cells(2, 8) = ""
    ' 請求日
    Sheet2.Cells(3, 8) = ""
    ' 件名
    Sheet2.Cells(6, 2) = ""
    ' 支払期限
    Sheet2.Cells(7, 2) = ""
    ' 振込先
    Sheet2.Cells(8, 2) = ""

    For iRow = 15 To 27
        ' 摘要
        Sheet2.Cells(iRow, 1) = ""
        ' 数量
        Sheet2.Cells(iRow, 4) = ""
        ' 単位
        Sheet2.Cells(iRow, 5) = ""
        ' 単価
        Sheet2.Cells(iRow, 6) = ""
        ' 税率
        Sheet2.Cells(iRow, 7) = ""
        ' 金額
        'Sheet2.Cells(iRow, 8) = ""
    Next iRow
        
End Sub

それぞれの箇所について説明を加えていきます。

Private Sub DataClear()

クリア処理の関数の定義を記述しています。名前をDataClearとしており、「引数」はありません。また、Privateと書かれている通り、他のモジュールからの呼び出しは行えないものとしています。

    ' 請求先名
    Sheet2.Cells(2, 1) = ""
    ' 請求No
    Sheet2.Cells(2, 8) = ""
    ' 請求日
    Sheet2.Cells(3, 8) = ""
    ' 件名
    Sheet2.Cells(6, 2) = ""
    ' 支払期限
    Sheet2.Cells(7, 2) = ""
    ' 振込先
    Sheet2.Cells(8, 2) = ""

ここでは「請求書」シートの中で「可変」データを入力するセルについてセルの内容をクリアさせています。

    For iRow = 15 To 27
        ' 摘要
        Sheet2.Cells(iRow, 1) = ""
        ' 数量
        Sheet2.Cells(iRow, 4) = ""
        ' 単位
        Sheet2.Cells(iRow, 5) = ""
        ' 単価
        Sheet2.Cells(iRow, 6) = ""
        ' 税率
        Sheet2.Cells(iRow, 7) = ""
        ' 金額
        'Sheet2.Cells(iRow, 8) = ""
    Next iRow

ここでは「請求書」シートの下部にある「摘要表」のセル情報をクリアさせています。
このサンプルでは「摘要表」は15行目～27行目まで入力可能ですので、iRow = 15 To 27として、ループをさせてクリアしています。

PDFファイル保存プログラム

Private Sub OutputPDF(fileName As String)
    Sheet2.ExportAsFixedFormat Type:=xlTypePDF, fileName:="C:\Users\***\Desktop\" & fileName
End Sub

PDFファイル生成処理の関数の定義を記述しています。名前をOutputPDFとしており、「引数」はfileNameとしています。ファイル名を「引数」として受け取ります。また、Privateと書かれている通り、他のモジュールからの呼び出しは行えないものとしています。
出力先はC:\Users\***\Desktop\としていますが、そのままでは動かないので、適宜変更をお願いします。

実行ボタンの実装

上部の「開発」タブを選択し、「デザインモード」ボタンを押下します。これにより、ボタンやテキストボックスの配置を変更できるモードになります。
それから「挿入」ボタンを押下すると、下図のように「フォーム　コントロール」が出てきます。赤枠にしているボタンアイコンを押下して、適切な場所にカーソルを置くと、ボタンが配置されます。

次に「デザインモード」の右にある「プロパティ」を押下します。
そうすると「プロパティダイアログ」が表示されますので、「(オブジェクト名)」を変更し（サンプルではcmdButtonとしています）、「Caption」も変更します（サンプルでは「実行」としています）。

それから「実行ボタン」をダブルクリックします。
「Microsoft Visual Basic for Applicatns」の画面が立ち上がり、Private Sub cmdButton_Click()というプログラムが表示されるので、以下のように先ほど作成したDataOutputを呼び出すようにします。

最後に、「デザインモード」ボタンを再度押下して、編集モードからマクロが実行できるモードに変更します。
これで準備完了です。

マクロ実行

では「請求書」シートの「実行ボタン」を押下してみます。
そうすると、私の場合はデスクトップにPDFファイルが3つ作成されることを確認しました。

また、「おそうじプロジェクト.pdf」を開いてみると、以下のようになっており、問題なくPDFファイルが作成されていることを確認することができました。

おわりに

今回は、PDFファイルの請求書を一括作成するプログラムについて、私が実際に行った内容を掲載させていただきました。どのように考えてプログラミングをしていくのかを記載したつもりです。
ただ、「なんでそんな発想ができるの？」とか、「そこは別のやり方があるやろ」など、いろいろ意見や質問があるかと思います。もし不明点やお聞きになられたいことがあれば、遠慮なくいつでもご連絡ください。