エクセルVBA｜差し込み印刷マクロをつかって請求書を連続で印刷する

本ブログでは以前の記事で、エクセルVBAでエクセルのシートや、グラフなどを印刷するマクロの作り方を紹介をしました。

エクセルVBAで印刷をするマクロでは”PrintOutメソッド“を使うことで、ブックやシート・グラフなどを指定する。または、まとめて印刷することができました。(以前の記事をご覧になっていない人はこちらからご覧いただくことができます。)

今回は以前の記事で紹介した印刷するマクロを応用して、売上履歴があった取引先に対して請求書の作成する処理をして、印刷をするまでを実行するマクロを作ってみたいと思います。

実際の仕事場で起こりうる作業なので、何をしているかのイメージがしやすいのではないでしょうか。

PR

差し込み印刷マクロを作るために準備したもの

エクセルVBAをつかって、差し込み印刷をするマクロをつくる上でいくつかのエクセルシートを準備しました。どこの職場や会社にでもあるようなものばかりなので、あなたの環境に置きかえて考えると、本記事で紹介するマクロの便利さが伝わりやすくなると思います。

差し込み印刷マクロを作るために準備したファイル(ワークブック)

1つのファイル(ブック)に3つのシートが作成されている状態です。画像の右下あたりにそれぞれのシート名が表示されいますが、このサンプルファイルではそれぞれのシートを”顧客リスト“と”販売記録”と”請求書“といった名前にしています。

それぞれのシートは以下のとおりです。

差し込み印刷マクロを作るために準備したワークシート：顧客リスト

差し込み印刷マクロを作るために準備したワークシート：販売記録

差し込み印刷マクロを作るために準備したワークシート：請求書の様式

PR

差し込み印刷マクロを作るためのながれ

エクセルVBAで差し込み印刷をするマクロとして以下の処理が必要になります。

1. 販売記録シートから、取引先と商品名／サービス名、単価・数量・金額を取得する
2. 取引先の数の分だけ請求書をコピーして、取引先名を宛先として入力する
3. 顧客リストシートから取引先の住所、1で取得したデータから請求明細を入力する
4. それぞれの取引先に向けた請求書を印刷する

それぞれの処理をコードにしていきましょう。今回のマクロは、上記の動作フロー図どおりの順番で動いていますが、はじめに処理をはじめるところが標準モジュールからとなるため、標準モジュールから紹介していきます。

差し込み印刷マクロを作るたのサンプルコード

標準モジュール

標準モジュールには、以下3つのプロシージャ書きこみます。

Option Explicit

Sub 請求書を削除する()

'画面更新を停止
Application.ScreenUpdating = False
'削除確認通知を停止
Application.DisplayAlerts = False

Dim ws As Worksheet'ワークシートを代入するオブジェクト変数の宣言
Dim myarr() As String'請求明細情報を代入する配列の宣言
Dim myarr2() As String'会社名を代入する配列の宣言
Dim msg As String'メッセージ代入用の配列を宣言

'すべてのシートを繰り返し判定して初期状態で存在するシート以外は削除する
For Each ws In Worksheets
    If ws.Name <> "顧客リスト" And ws.Name <> "販売記録" And ws.Name <> "請求書" Then
        ws.Delete
    End If
Next ws
  
'画面更新を再開
Application.ScreenUpdating = True
'削除確認通知を停止
Application.DisplayAlerts = True


'販売記録シートモジュールのプロシージャを呼び出し戻り値を受け取る
myarr2 = Sheet2.辞書を作成し各社請求額を集計する()'会社名だけの配列
myarr = Sheet2.配列で各社の売上記録を整理する()'請求明細情報の配列
Call 請求書を作成する(myarr, myarr2)'配列を引数としてモジュールを呼び出す


msg = MsgBox("請求書の作成が完了しました。" & vbCrLf & _
            "引続き印刷を実行しますか？", vbYesNo, "印刷前確認")

If msg = vbYes Then
    Call PrintOutメソッドによる印刷'[はい]なら印刷を実行するプロシージャを呼び出す
Else
    MsgBox "印刷処理はキャンセルされました。マクロを終了します。", vbOKOnly
    Exit Sub'[いいえ]なら処理を終了
End If

End Sub


'*****************************
Sub 請求書を作成する(myarr, myarr2)
    '画面更新を停止
    Application.ScreenUpdating = False
        
    Dim i As Long, j As Long, k As Long'カウンタ変数を宣言
    Dim ro As Long'顧客リストシートの最終行を代入する変数を宣言
    Dim Ls As Worksheet'顧客リストシートを参照するオブジェクト変数を宣言
    Dim Sr As Range'顧客リストシートのセル範囲を参照するオブジェクト変数を宣言

    For i = 0 To UBound(myarr2)'販売記録にある会社名数分だけ繰り返す
        
'***請求情報がある取引先分のシートをコピー***
        
         ThisWorkbook.Sheets("請求書").Copy _
        After:=ThisWorkbook.Worksheets(Worksheets.Count)

        ActiveSheet.Name = myarr2(i) 'シート名を取引先の会社名にする
        ThisWorkbook.Worksheets(myarr2(i)).Cells(3, 1) = myarr2(i) '請求書の宛先に取引先の社名を入力
        
        Set Ls = ThisWorkbook.Worksheets("顧客リスト")'顧客リストシートを代入する
        ro = Ls.Cells(Rows.Count, 3).End(xlUp).Row
        Set Sr = Ls.Range(Ls.Cells(2, 3), Ls.Cells(ro, 7))
        
        'VLookup関数で顧客リストから郵便番号と住所を抽出
        ActiveSheet.Cells(4, 1) = "〒" & _
        WorksheetFunction.VLookup(myarr2(i), Sr, 2, False) '郵便番号
        
        ActiveSheet.Cells(5, 1) = _
        WorksheetFunction.VLookup(myarr2(i), Sr, 3, False) & _
        WorksheetFunction.VLookup(myarr2(i), Sr, 4, False) '住所
        
        If Month(Date) <> 12 Then '12月以外は翌月末日を期限にする
            ActiveSheet.Cells(7, 8) = DateSerial(Year(Date), Month(Date) + 1, 0)  '請求日は当月末を記入する
            ActiveSheet.Cells(8, 8) = DateSerial(Year(Date), Month(Date) + 2, 0) '支払い期限は翌月末を記入する
        Else '12月の場合は年にも1を足すための分岐
            ActiveSheet.Cells(7, 8) = DateSerial(Year(Date), Month(Date) + 1, 0)  '請求日は当月末を記入する
            ActiveSheet.Cells(8, 8) = DateSerial(Year(Date) + 1, Month(Date) + 2, 0) '支払い期限は翌月末を記入する
        End If
        
'***品目と請求内訳を入力するマクロ***
        k = 11
        For j = 0 To UBound(myarr)
            If ActiveSheet.Name = myarr(j, 0) Then
                ActiveSheet.Cells(k, 1) = myarr(j, 1) '品目を入力
                ActiveSheet.Cells(k, 6) = myarr(j, 2) '単価を入力
                ActiveSheet.Cells(k, 7) = myarr(j, 3) '数量を入力
                ActiveSheet.Cells(k, 8) = myarr(j, 4) '金額を入力
                
                k = k + 1
                If k >= 21 Then  '取引件数の判定
                    MsgBox "当月の取引件数が10件以上になる場合は個別に印刷してください。" & vbCrLf & _
                                "つぎの取引先に処理が移ります。", vbOKOnly
                    
                    Exit For 'つぎのシートに処理を移行する。
                End If
            End If
        Next j
    Next i
    
    '画面更新を再開
    Application.ScreenUpdating = True
        
End Sub

'*****************************
Sub PrintOutメソッドによる印刷()

Dim ws As Worksheet
    
    For Each ws In Worksheets
        If ws.Name <> "顧客リスト" And ws.Name <> "販売記録" And ws.Name <> "請求書" Then
            '「Microsoft Print to PDF」をプリンターとして指定する
            ws.PrintOut preview:=True, ActivePrinter:="Microsoft Print to PDF"
        End If
    Next ws

End Sub

まずは標準モジュールで実行したい処理を書きこみました。ただし、標準モジュールのプロシージャが正常に処理を実行するために必要な材料(配列データ)を作らなければならないので、それらは販売記録シートモジュールに書いています。販売記録シートモジュールのコードは、標準モジュールのコード解説のあとで解説します。

標準モジュールに書いたVBAコードの解説

Sub 請求書を削除するプロシージャについて

このプロシージャでは、まず既にシートが作成してあった場合をみこして、必要最低限の“顧客リスト”、”売上記録”、”請求書”シート以外のシートを削除する処理を実行します。これは実際の会社での作業を想定すると前月の作業でシートが残っている状態があるためです。

つづいて、販売記録シートモジュールのFunctionプロシージャを呼び出します。販売記録シートモジュールのプロシージャは、本記事の後半で詳しく解説しますが、今の時点ではこのプロシージャから別のプロシージャを呼び出して、なんらかの処理によってできた結果を受け取りたいと考えてもらえば良いです。

1つめに呼び出すプロシージャは『辞書を作成し各社請求額を集計する』ですが、このプロシージャで処理した結果として戻り値を配列の myarr2 に受け取ります。

次に呼び出すプロシージャは『配列で各社の売上記録を整理する』です。
このFunctionプロシージャで処理をした結果の戻り値を配列 myarr に受け取ります。

さらに、Callステートメントをつかって標準モジュールの『請求書を作成するプロシージャ』を呼び出します。 myarr , myarr2 を引数として呼び出すプロシージャに渡すところがポイントです。

1つのモジュールにすべての処理をまとめられますが、コードが長文になったり、問題が発生した場合の対応したことが他のコードに影響をあたえるリスクなど、特定の処理ごとに部品としてまとめることは、後のことを考えた方法と言えます。

Sub 請求書を作成するプロシージャについて

このプロシージャでいよいよ請求書を作成していきます。VBAのコードが長いのでポイントを取り出しながら解説していきます。

販売記録シートに入力された会社の数に応じて処理を繰り返すように回数を指定する

For i = 0 To UBound(myarr2)

まずは、販売記録シートに取引があった会社の数だけシートを作成する必要があるため、For文をつかって繰り返し回数を指定していきます。ここでは事前に取得しておいた配列 myarr2 から回数を取得していきます。
取得する方法は、配列の上限インデックスが取得できる『 UBound関数 』を使います。例えば myarr2 の上限インデックスが5であった場合、変数 i が、0から5までの6回処理を繰り返す処理です。

繰り返し処理については、以下の記事で詳しく解説していますので参考にしてください。

つづいて処理の内容を作っていきます。

請求書様式のシートを末尾に追加して、シート名と請求書の宛先に取引先の会社名を入力する

ThisWorkbook.Sheets(“請求書”).Copy _
After:=ThisWorkbook.Worksheets(Worksheets.Count)

最初に”請求書”シートをコピーして、末尾に追加しています。
シートの追加を実行すると、追加したシートがアクティブになり、”ActiveSheet”で指定できます。シート名を取引先の会社名に変更したいので、配列 myarr2 に代入された会社名から取得し、追加した請求書様式のシート名を取引先の会社名に変更していきます。
また、上記の処理と合わせて、宛先を入力しているセル(A3)に入力します。

請求書の宛先に入力された会社名の郵便番号と住所をVLookup関数で取得する

顧客リストシートから、VLookup関数を使って郵便番号と住所を取得したいので、引数をそれぞれの変数に代入し、準備していきます。

Set Ls = ThisWorkbook.Worksheets(“顧客リスト”)

顧客リストシートをシートオブジェクト変数(Ls)に代入します。この処理は、請求書に取引先の会社の郵便番号や住所を取得するための事前準備です。このサンプルマクロでは“VLookup関数”をつかって必要な値を取得してみたいと思います。

Set Sr = Ls.Range(Ls.Cells(2, 3), Ls.Cells(ro, 7))

VLookup関数の引数である検索範囲を取得したいので、変数(Ls)に代入した顧客リストシートからセル範囲(C列3行目のセルから、G列最終行セル)を変数(Sr)代入します。

VLookup関数では引数に”検索値”、”検索範囲“、”列数“、”検索の型“が必要です。変数(Sr)に検索範囲が代入できたところで、VLookup関数をつかうために必要な引数がそろいました。さっそくVLookup関数をつかって郵便番号と住所を取り出していきます。

VLookup関数については、以下の記事で解説をしています。詳しい内容について知りたい人はご覧ください。

WorksheetFunction.VLookup(myarr2(i), Sr, 2, False)

取引先の会社名を検索値として、変数(Sr)のセル範囲から郵便番号を取り出します。取り出した値を追加した請求書のA4セルに入力します。

WorksheetFunction.VLookup(myarr2(i), Sr, 3, False) & _
WorksheetFunction.VLookup(myarr2(i), Sr, 4, False) ‘住所

取引先の会社名を検索値として、変数(Sr)のセル範囲から住所1と住所2を取り出します。取り出した値を追加した請求書のA5セルに入力します。

請求書の請求日と支払期限を入力する

請求書には、請求日や支払期限を入力することがありますので、こちらもVBAで入力していきます。このマクロでは、請求日は当月の末日とし、支払期限は翌月の末日で入力をしていきます。

月末の日付と翌月末の日付を取得するには、DateSerial関数を使います。DateSerial関数は引数として指定した年、月、日から日付を返します。

DateSerial(year, month, day)

引数の項目	必須／省略可能	説明
year	必須	100 以上 9999 以下の数値、または数式で指定することができます。
month		任意の数式で指定することができます。
day		任意の数式で指定することができます。

Sub DateSerial関数()

    Debug.Print DateSerial(2000, 10, 20) '2000年10月20日

End Sub

2000/10/20

DateSerial関数の引数に、Year関数とMonth関数を使います。さらにそれぞれの関数の引数にDate関数を指定することで、現在の日付から年と月を取得できます。
また、年をまたいだときのことを想定して、現在の日付が12月である場合のみ支払期限を翌年にする処理に分岐するようにしておきます。

ここまでの処理で、請求書シートの状態は以下の画像のとおりです。

引き続き、請求書の品目と請求明細の入力処理を見ていきます。

請求書の品目と請求内訳を入力する

ここからは、それぞれの取引先の販売記録を取得して請求書に入力していきます。この処理では、事前に取得した販売記録のデータである配列 myarr から必要な値を取り出していきます。

既に別のプロシージャでも紹介していますが、myarr には販売記録から取り出した以下の配列データが代入されています。前項では取引先の会社名を検索値として、VLookup関数で顧客リストシートから必要な値を取得する方法にしましたが、こちらでは、配列をつかった方法で取り出しています。

具体的には、配列 myarr の一番左の列(取引先名)の値が、追加したシートのシート名と一致したら、請求書の明細として、品目やサービス名、単価、数量、金額を請求書に入力する処理を実行します。この結果、シート名(取引会社名)に一致する取引記録が請求書に入力されていきます。

ここで注意する点として、複数の変数を操作する点です。サンプルコードの処理で使用している変数は j 、k の2種類をつかっていますが、それぞれ変数は以下の役割に使用しています。

変数の名称	変数の役割
j	配列 myarr の行数としての役割があり、配列 myarr のなかを繰り返し(ループ)処理に利用しています。 配列のインデックスが0から最大値になるまで処理を繰り返します。
k	請求書のセルを指定する役割をしています。 請求書の様式にあわせて、ここでは11(行目)から開始します。

変数 j については、If文によるシート名との判定で一致する、一致しないにかかわらずループしていきますが、変数 k は判定に一致した場合のみ数を加算していきます。これはセルに請求情報を入力した処理が実行されたときだけ行数を変更していくためです。

変数 k は、If文による判定で不一致となり、セルになにも入力をしないときも更新してしまうと、請求明細に空白行が出てしまいます。そのため、変数 j と k がそれぞれ独立した繰り返し(ループ)処理をする必要があるわけです。

さらに、もう1点注意しておきたいのが入力する行がすべてうまってしまう可能性がある点です。
サンプルの請求書の様式では、10行分の余白がありますが、取引明細が10件以上となった場合、メッセージボックスを表示するような対処をしています。

行数を判定しているコードは以下です。請求書の様式では、20行目まで明細が入力できる行となっています。そのため、行数を持つ変数 k が21以上になったときは他の分岐した処理を実行します。

If k >= 21 Then

今回のサンプルでは、10以上の明細となるケースが特殊であると捉えて、ユーザーにメッセージボックスでそれ以上の明細は入力できない旨の通知をする対処にしています。

ここまでの処理で、取引記録シートにある取引先ごとの請求書の作成と請求明細の入力が完了しました。最後に印刷をするマクロを作成していきましょう。

Sub PrintOutメソッドによる印刷プロシージャについて

これまでの処理で請求書の作成が完了しているため、ひきつづき請求書を印刷するプロシージャを作成していきます。ここでは、以前にも紹介したPrintOutメソッドを使っていきます。

ws.PrintOut preview:=True, ActivePrinter:=”Microsoft Print to PDF”

上記のコードを使うことでワークシートの印刷を実行しますが、不要なシートまで印刷をしてしまわないようにIf文を利用して、”顧客リスト“、”販売記録“、”請求書“というシートネームのワークシートは印刷しないように判定しています。

PrintOutメソッドをつかったマクロで印刷を自動化する方法については、以前の記事で紹介しています。
　くわしくは以下のリンクよりご覧ください。

販売記録のシートモジュール

販売記録のシートモジュールに2つのプロシージャを書きこみます。

Option Explicit

Public Function 辞書を作成し各社請求額を集計する()
    
    Dim mydic As Dictionary '辞書を宣言
    Set mydic = New Dictionary
    
    Dim myarr2() As String '動的配列を宣言
    Dim i As Long, Price As Long 'カウンタ変数と各取引先ごとの売上を代入する変数を宣言
    Dim CN As String '取引先の会社名を代入する変数を宣言
    
    With Me
        '重複しないリストを作成し、取引先ごとの売上を集計する
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            CN = .Cells(i, 2).Text: Price = .Cells(i, 6).Text
            If Not mydic.exists(CN) Then'取引先名が辞書オブジェクトに既に登録されていないかを判定
                mydic.Add .Cells(i, 2).Text, .Cells(i, 6).Value
            Else
                mydic(CN) = mydic(CN) + Price
            End If
        Next i
        
        '辞書オブジェクトに含まれているデータの組み数分の配列を再定義
        ReDim myarr2(mydic.Count - 1)
        
        '辞書オブジェクトに含まれている会社名を配列に代入する
        For i = 0 To mydic.Count - 1
            myarr2(i) = mydic.Keys(i)
        '各取引先の売上合計をイミディエイトウィンドウに出力する(マクロ作成者確認用)
            Debug.Print mydic.Keys(i), mydic.items(i)
        Next i
        
    End With
    
    '配列を戻り値として指定する
    辞書を作成し各社請求額を集計する = myarr2
    
    '辞書オブジェクトを削除する
    Set mydic = Nothing

End Function


'*****************************
Public Function 配列で各社の売上記録を整理する()

    Dim myarr() As String '配列を宣言
    Dim i As Long 'カウンタ変数を宣言
    Dim Tno As Long '配列の要素数を定義するための変数を宣言
    
    
    With Me
        '配列の大きさを行数から調べる(サンプルでは18行)
        Tno = .Cells(Rows.Count, 3).End(xlUp).Row - 2
        
        '二次元配列を再定義する(サンプルでは18行の5列)
        ReDim myarr(Tno, 4) As String
        
        '二次元配列に販売記録シートの値を代入する
        For i = 2 To .Cells(Rows.Count, 3).End(xlUp).Row
            myarr(i - 2, 0) = .Cells(i, 2).Text '取引先を代入
            myarr(i - 2, 1) = .Cells(i, 3).Text '商品名／サービス名を代入
            myarr(i - 2, 2) = .Cells(i, 4).Value '単価を代入
            myarr(i - 2, 3) = .Cells(i, 5).Value '数量を代入
            myarr(i - 2, 4) = .Cells(i, 6).Value '金額を代入
        Next i
    End With
    
    '配列を戻り値として指定する
    配列で各社の売上記録を整理する = myarr

End Function

販売記録シートモジュールに書いたVBAコードの解説

Public Function 辞書を作成し各社請求額を集計するプロシージャについて

ここでは、当月に取引があった会社の数が取得したいので、辞書(Dictionary)オブジェクトを宣言して重複しないリストをつくります。なぜ、取引のあった会社の数を知りたいかですが、それは取引先ごとに請求書を作成するため、請求書シートをいくつコピーすれば良いのかが知りたいからです。

辞書(Dictionary)オブジェクトは、キーと値を組み合わせてデータを代入できますが、こちらのサンプルコードでは、キーに取引先の会社名を登録、アイテムに金額を登録する処理をしています。

辞書(Dictionary)オブジェクトは、キーが重複ができない性質をもっているため、販売記録シートを上から順にみていき、既に登録されているキー(会社名)であった場合は、既存の会社名のアイテム(金額)に足していく処理をしています。

辞書(Dictionary)オブジェクトにデータを代入したあとは、配列である myarr2 に辞書(Dictionary)のキーをすべて代入します。 これにより重複のない販売記録シートに入力されている会社名のリストが作成できました。

配列 myarr2 には、11社の取引先が登録されたことで、請求書シートは11枚必要があることがわかります。

辞書(Dictionary)オブジェクトの特徴や基本的な使い方については、こちらの記事で紹介していますので、ぜひご覧ください。

Public Function 配列で各社の売上記録を整理するプロシージャについて

こちらのFunctionプロシージャでは、コピーした請求書シートに明細を書き込むために販売記録シートに入力された値を配列に代入していきます。代入していく値は、販売記録シートの取引先名、商品名／サービス名、単価、数量、金額に代入し、販売記録シートのような二次元配列を作っていきます。

Functionプロシージャの詳しい解説についてはこちらの記事をご覧ください。

販売記録シートモジュールに書いたVBAで作成した配列データ

販売記録シートモジュールに書いたVBAで、配列データをつくるイメージを画像で紹介します。

販売記録シートモジュールは、標準モジュールの”Sub 請求書を作成する(myarr, myarr2)”で処理を実行するための材料(配列データ)を作っています。

差し込み印刷マクロをVLookupで連続印刷を自動化するマクロ解説まとめ

おさらいになりますが、この記事で解説したマクロは以下の処理を連携させています。

1. 標準モジュール(Sub 請求書を削除する)
   すでに作成されていた請求書を一度削除するマクロを実行
2. 販売記録シートモジュール(Public Function 辞書を作成し各社請求額を集計する)
   請求書のいくつ作れば良いかの情報が欲しいので、辞書(mydic)を利用して販売記録シートの取引先名だけの配列(myarr2)をつくる
3. 販売記録シートモジュール(Public Function 配列で各社の売上記録を整理する)
   請求書の明細情報が欲しいので、販売記録シートの配列(myarr)をつくる
4. 標準モジュール(Sub 請求書を作成する)
   販売記録シートモジュールで取得した配列データを使って、請求書を必要な分だけ作成する
5. 標準モジュール(Sub PrintOutメソッドによる印刷)
   作成した請求書を印刷する
   

本記事では、請求書の作成と印刷までを自動化するマクロをエクセルVBAでつくる方法について解説しました。VBAコードは長文となって難しいように感じるかもしれませんが、細かい処理にわけて考えるとそれぞれの処理自体は難しいものではありません。

今回紹介したマクロで処理していることを、毎回人の手で作業していることを考えると作業の負担を軽くしたり、時間を短縮できるメリットは大きいと感じていただけるのでしたら、ぜひこういったマクロを作ることにも挑戦してみてはいかがでしょうか。