Excelマクロで仕事効率化（初心者向け）(3) | 一括請求書PDF作成
前回は、請求書を作成するためのファンデーション（基礎）について説明をいたしました。これにより、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 SubPDFファイル生成処理の関数の定義を記述しています。名前を`OutputPDF`としており、「引数」は`fileName`としています。ファイル名を「引数」として受け取ります。また、`Private`と書かれている通り、他のモジュールからの呼び出しは行えないものとしています。
出力先は`C:\Users***\Desktop`としていますが、そのままでは動かないので、適宜変更をお願いします。
実行ボタンの実装
上部の「開発」タブを選択し、「デザインモード」ボタンを押下します。これにより、ボタンやテキストボックスの配置を変更できるモードになります。
それから「挿入」ボタンを押下すると、下図のように「フォーム コントロール」が出てきます。赤枠にしているボタンアイコンを押下して、適切な場所にカーソルを置くと、ボタンが配置されます。
次に「デザインモード」の右にある「プロパティ」を押下します。
そうすると「プロパティダイアログ」が表示されますので、「(オブジェクト名)」を変更し（サンプルでは`cmdButton`としています）、「Caption」も変更します（サンプルでは「実行」としています）。
それから「実行ボタン」をダブルクリックします。
「Microsoft Visual Basic for Applicatns」の画面が立ち上がり、`Private Sub cmdButton_Click()`というプログラムが表示されるので、以下のように先ほど作成した`DataOutput`を呼び出すようにします。
最後に、「デザインモード」ボタンを再度押下して、編集モードからマクロが実行できるモードに変更します。
これで準備完了です。
マクロ実行
では「請求書」シートの「実行ボタン」を押下してみます。
そうすると、私の場合はデスクトップにPDFファイルが3つ作成されることを確認しました。
また、「おそうじプロジェクト.pdf」を開いてみると、以下のようになっており、問題なくPDFファイルが作成されていることを確認することができました。
おわりに
今回は、PDFファイルの請求書を一括作成するプログラムについて、私が実際に行った内容を掲載させていただきました。どのように考えてプログラミングをしていくのかを記載したつもりです。
ただ、「なんでそんな発想ができるの？」とか、「そこは別のやり方があるやろ」など、いろいろ意見や質問があるかと思います。もし不明点やお聞きになられたいことがあれば、遠慮なくいつでもご連絡ください。
この記事が気に入ったらサポートをしてみませんか？