ノン・プログラマー、仕事のためのマクロ・VBA(4)Excel差し込み印刷で、複数の見積書作成を自動化

  • ブックマーク
  • Feedly
  • -
    コピー

マイクロソフトWordの「差し込み印刷」を使用したことがある人は、その便利さを実感していることでしょう。

何十種類ものビジネス文書を、ひとつずつ目視・手動でカスタマイズする煩わしさから解放される快感。

一度覚えたら、忘れられないテクニックのひとつです。同じ要領でExcelでも...、できないんですよね。

残念ながらExcelの基本メニューには、「差し込み」という機能はありません。

でも、大丈夫!! ごく簡単なVBAプログラムを組めば「差し込み印刷」に対応。

しかも、メールでも送れるように、PDFエクスポートもしてくれるんです。

(さらなるツワモノは、OutlookをVBAで操作して、出来上がったPDFを、自動で全件別の宛名にメール送信してしまいます。そこまでの話となると、本記事の範囲を超えますので、気になった方は、ぜひ一度お問い合わせ下さい。)

この記事では、発注元ごとに異なる件数の発注情報を、1件1ワークシートの見積書テンプレートに「差し込み」して、PDFエクスポートをするプログラムについてご紹介します。

Excel差し込み印刷で、やりたいこと

ノン・プログラマー、仕事のためのマクロ・VBA(4)Excel差し込み印刷で、複数の見積書作成を自動化

上記のような見積書テンプレートを使い、自社取扱品目について、複数の見積書を作成すると仮定します。

これとは別に、各営業マンが得意先から聞いてきた発注情報がまとめられている、とします。

ノン・プログラマー、仕事のためのマクロ・VBA(4)Excel差し込み印刷で、複数の見積書作成を自動化

これを、どうやって別々の見積書にまとめますか?

1件ずつ、コピー&ペースト(コピペ)ですか? お疲れ様です。

見本では3件の情報しかありませんが、得意先が100件あったらどうしましょう。

商品点数も、普通は10点程度では済まないですよね?

もしコピペのミスをして、誤った金額の見積書を作ってしまったら?

上司が菓子折りを持ってお詫びに急行。その後、社内で大目玉をくらうのは確実ですよね。

ヒューマンエラーを起こさないように、機械に自動的にコピペをしてもらいましょう。

今回作成したプログラムがこちらです。

'受注表から、社名、受注数量をコピーして、見積書に自動貼り付けするプログラム
Sub insert_export()


Dim i, j, k, lastcolumn As Integer
'繰返し回数と最終列をカウントするための変数宣言

    lastcolumn = Worksheets("list").Range("B1").End(xlToRight).Column
    '受注表に、何店分の受注データがあるか、店数を数えるカウンタ

    i = 2
    '受注表の店別受注内訳がB列(2列目)からはじまっているので、
    '店の列数を数えるためのカウンタ初期値は2と指定

        Do

        Sheets("list").Cells(1, i).Copy

        Sheets("estimate").Range("B4").PasteSpecial (xlPasteValues)
        '受注表の発注元名を、見積書シートの「宛名」欄に転記
        

            For j = 2 To 6

                Sheets("list").Cells(j, i).Copy

                Sheets("estimate").Cells(j + 13, 3).PasteSpecial (xlPasteValues)

            Next j
            '受注表の【インテリア雑貨】(2-6行目)を見積書の15-19行目に転記

            For k = 7 To 11

                Sheets("list").Cells(k, i).Copy

                Sheets("estimate").Cells(k + 14, 3).PasteSpecial (xlPasteValues)

            Next k
            '受注表の【掃除用具】(7-11行目)を見積書の21-25行目に転記


        Sheets("estimate").ExportAsFixedFormat Type:=xlPDF, Filename:=ThisWorkbook.Path & "\" & Sheets("estimate").Range("B4") & ".pdf"
        '転記が済んだ見積書を、発注元店名をファイル名とするPDFにエクスポート


        Sheets("estimate").Range("B4").ClearContents

        Sheets("estimate").Range("C15:C19").ClearContents

        Sheets("estimate").Range("C21:C25").ClearContents
        'PDFエクスポート後の見積書シートを、きれいにデータ消去

    i = i + 1
    '次の発注元情報の列に移動
    
    Loop Until i = lastcolumn + 1
    '最終列の発注元分までPDFを作り終えたら、繰返し処理(ループ)終了

End Sub

「差し込み」プログラムがやっていること

ノン・プログラマー、仕事のためのマクロ・VBA(4)Excel差し込み印刷で、複数の見積書作成を自動化

詳しくは、実際のコードをお読みいただくこととして、このプログラムで実現しようとしていることは、

(1)右から左へ終端セルを検索する構文(End(xlToRight))で、シート名「list」(≪発注表≫)の中に、何件分の発注情報があるかを数えます(B列からE列までの3件分でした)。

lastcolumn = Worksheets("list").Range("B1").End(xlToRight).Column

(2)「List」の1行目(発注元店名)を見積書の宛名欄に、「List」の2-6行目(【インテリア雑貨】の発注数量)、7-11行目(【掃除用具】の発注数量)を、それぞれ見積書の数量欄に「値コピー」します。

(例)

Sheets("list").Cells(1, i).Copy
Sheets("estimate").Range("B4").PasteSpecial (xlPasteValues)

(3)発注数量がコピーされて完成した見積書を、PDFとして保存します。

Sheets("estimate").ExportAsFixedFormat Type:=xlPDF, Filename:=ThisWorkbook.Path & "\" & Sheets("estimate").Range("B4") & ".pdf"

(4)エクスポートが完了した見積書のテンプレートからは、宛名、発注数量の情報を全てクリアにします。

(例)

Sheets("estimate").Range("B4").ClearContents

(5)上記(1)-(4)の作業を、3回(3件分)繰り返します。

という一連の作業の自動化でした。

まとめ:差し込み印刷はWordだけじゃない。Excelでも「繰り返し」と「終端セル検索」VBAの応用で、ミス激減の自動化が達成できます。

Excelでの差し込み印刷自動化が、それほど難しい話ではないことがご理解いただけましたでしょうか?

お気づきかもしれませんが、押さえておくべきことは、実は本シリーズ(2)でご説明した「繰り返し構文(For...Next)」※または、Do...Loop Untilと終端セル検索のEnd(xlToUp) ※ここではEndxlToRight を応用しただけのこと、という点ですね。

これらの構文は、応用範囲がとても広いので、他にもまだ様々な転用が可能です。使いこなせるようになるために、ぜひ他の場面でも活用方法をいろいろ考えてみましょう。









小さなお悩みでも、
お気軽にご相談ください!

お急ぎの方はお電話にてお問い合わせください

050-6867-2130
せるワザロゴ

セルワザでは、役に立つEXCELワザをご紹介しております!

  • ブックマーク
  • Feedly
  • -
    コピー