【業務効率化】エクセルで一括で請求書を自動作成するマクロを作ってみた

エクセルで従業員情報や顧客情報などを管理している企業は多くあると思います。また、DBなどからcsvで出力しそこから請求書などを作成している企業も。
また、システムがあるのでDB（RDB）からCSVでしか出力できないケースもあると思います。

そこでエクセルを使って、
・エクセルに張り付ける
・ボタンを押す

で自動で行ごとに一気に出力するマクロ機能を使ったエクセルを作成してみました。
利用用途としては、請求書や辞令などの書類を同じテンプレートで出すケースです。結構多岐にわたるかと思います。コードを参考にしてすでに組み込まれたコードを改良していただいてもOKです。

そもそもマクロって何？

ここでいうマクロとは「複数の操作をまとめて必要に応じて呼び出せるようにする機能」を指します。
また一般的にマクロというとマイクロソフトのエクセルのマクロが多く使われます。
もちろんパワポやワードでもマクロは使えます。実際には、記録マクロとVBAなどのコードを使ったマクロがあります。

マクロとVBAって何？違いは？

VBA【Visual Basic for Applications】の略称でプログラミング言語のことです。
マクロは自動化させる機能そのものを指します。
なので「VBAを使ってマクロを動かす。」このような関係値です。

請求書等作成に役立つマクロの実際のコード

では早速そのコードです。

Sub ExportDataToPDF()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, j As Long, lastCol As Long
    Dim wordApp As Object, wordDoc As Object
    Dim employeeData As Variant, headerData As Variant
    Dim desktopPath As String, folderPath As String, pdfPath As String
    Dim fsObj As Object
    Dim templatePath As String
    Dim placeholderText As String

    ' Excelのワークシートを設定
    Set ws = ThisWorkbook.Sheets("一括作成")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' ヘッダーの読み込み
    headerData = ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol)).Value
    
    ' Wordとファイルシステムのオブジェクトを初期化
    Set wordApp = CreateObject("Word.Application")
    Set fsObj = CreateObject("Scripting.FileSystemObject")
    desktopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")

    ' Excelファイルと同じフォルダにあるWordテンプレートのパスを設定
    templatePath = ThisWorkbook.Path & "\テンプレート\テンプレート賞与.docx"
    
    ' Excelの各行に対してループ
    For i = 2 To lastRow
        employeeData = ws.Range(ws.Cells(i, 1), ws.Cells(i, lastCol)).Value

        ' Wordテンプレートを開いてプレースホルダを置換
        Set wordDoc = wordApp.Documents.Open(templatePath)
        With wordDoc
            ' ヘッダーに対応するデータでプレースホルダを置換
            For j = 1 To lastCol
                placeholderText = "[" & headerData(1, j) & "]"
                .Content.Find.Execute FindText:=placeholderText, ReplaceWith:=employeeData(1, j)
            Next j

            ' 所属に基づいてフォルダを作成し、PDFとして保存
            folderPath = ThisWorkbook.Path & "\" & employeeData(1, 3)
            If Not fsObj.FolderExists(folderPath) Then
                fsObj.CreateFolder folderPath
            End If
            
            pdfPath = folderPath & "\【賞与】" & employeeData(1, 1) & " " & employeeData(1, 2) & " " & Format(DateAdd("m", 1, Date), "yyyymm") & ".pdf"

            ' PDF形式で名前を付けて保存
            .SaveAs2 pdfPath, FileFormat:=17 ' 17 = wdFormatPDF
            .Close False
        End With
    Next i

    ' オブジェクトのクリーンアップ
    wordApp.Quit
    Set wordDoc = Nothing
    Set wordApp = Nothing
    Set fsObj = Nothing
End Sub

ちなみに、フォルダ階層はこんな感じです。

一括作成フォルダ1.0.0/
    ├── テンプレート/
    │   ├── テンプレート賞与.docx
    │   └── テンプレート辞令.docx
    └── 一括作成.xlsm

ダウンロードデータもどうぞ。
すぐに挙動を試して仕様がわかるように簡単なダミーのテンプレートとデータを入れてあります。

ダウンロードファイルはzip形式ですのでそのまま展開していただければOKです。コピーして組み込んでもOKです。

実際にマクロの使用方法について

基本的にはエクセルを開き、マクロを有効にしてボタンを押せば同じフォルダ内に行ごとのデータを特定のMicrosoft Wordに挿入されていきます。一度利用すれば仕様がわかるかと思います。
後述しますが、テンプレートへの参照先を変えたい時はワード名を変えるだけで挿入先を変えることが可能です。好きなテンプレート作成してみてください。

フォルダ参照もできる限り相対参照を利用しています。なのでフォルダ階層を崩さず使えばパスの設定もそこまで難しくありません。
なお、生成するファイル名も行の値を使って作成。重複しないように年月も挿入。
保存の仕方も特定の列ごとに所属などがあるケースでは自動仕分けされる仕様です。

マクロのコード解説

まずはコード自体にコメントをしてあるので各処理をご確認ください。
内容を理解することで修正に対応できると思いますので、細かく解説をしていきます。

    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, j As Long, lastCol As Long
    Dim wordApp As Object, wordDoc As Object
    Dim employeeData As Variant, headerData As Variant
    Dim desktopPath As String, folderPath As String, pdfPath As String
    Dim fsObj As Object
    Dim templatePath As String
    Dim placeholderText As String

まず最初のここですが例えば一つわかりやすいものを出すと
「templatePath（という文字のところ）」には（As)「String（文字列型）」のものが入る設定でいくよ
という初期設定のようなものです。

続いて、

    ' Excelのワークシートを設定
    Set ws = ThisWorkbook.Sheets("一括作成")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

ここの「ThisWorkbook.Sheets(“一括作成”)」がシート名です。
シート名を変えたときはここを変えてください。

続いて、

    ' ヘッダーの読み込み
    headerData = ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol)).Value

これはエクセルのヘッダーがどこにあるかを指定しています。
１行目の（1,1）から（1,最後）まですべてヘッダーだよ。と命令しています。
「lastCol」を利用することで横にどんなに長くなっても対応できるようにしてあります

一般的にエクセルで１行目をヘッダーにすると思いますので変更する必要はないかもしれません。
ここの値が自体がワードに挿入ときの指定ワードになります。ワードに入力するときは[名前]のようにカラム名を指定してください。詳しくはダミーテンプレートをご確認いただければわかりやすいかと思います。

続いて特に変更しそうなところを解説します。

・データ挿入先テンプレートについて

    ' Excelファイルと同じフォルダにあるWordテンプレートのパスを設定
    templatePath = ThisWorkbook.Path & "\テンプレート\テンプレート賞与.docx"

要約すると

「templatePath」はこのファイルのある同じ階層のテンプレートというフォルダの中にある「テンプレート賞与.docx」というファイルのパスに設定します。

という意味です。

なのでもし出力したいテンプレートが変わったときは同じテンプレートフォルダ内に適当な名前を付けて入れて「テンプレート賞与.docx」を「〇〇〇.docx」に書き換えればOKです。
テンプレートフォルダ内にはいくつファイルがあってもOKです。コードをコピーして関数を複数作れば同じファイルで同時に利用することも可能です。

・保存仕分けについて

            ' 所属に基づいてフォルダを作成し、PDFとして保存
            folderPath = ThisWorkbook.Path & "\" & employeeData(1, 3)
            If Not fsObj.FolderExists(folderPath) Then
                fsObj.CreateFolder folderPath
            End If

ダミーデータで試した方はわかるかもしれませんがボタンを押して待つと、同じフォルダ内に部署ごとに分かれます。これは上記の部分でどの分類ごとで別れるかを指定しています。

データについては、
employeeData(1, 3)
で3列目を指定しているため３列目にある「所属」が選ばれているだけです。
そのためこの後ろの数字の3を変えればどの列でまとめるかを指定することができます。

・フォルダ名について

            pdfPath = folderPath & "\【賞与】" & employeeData(1, 1) & " " & employeeData(1, 2) & " " & Format(DateAdd("m", 1, Date), "yyyymm") & ".pdf"

これは出力する際のデータのファイル名を自動で作成しています。
具体的には、出力する際のフォルダの名前を１行目１列と１行目２列目を指定しています。

ここで注意です。１行目の１列を指定していますが以前の行で「employeeData」は２列目からループさせています。そのため、正確にはここでの１行目１列はA2です。つまり、データの１行目を指しています。
もし、タイトル名を変えたい時は行、列の順番に並んでいますので、後半の数字を変えればＯＫです。例えば1,3とかにすれば所属が入ります。

ちなみにここも数字ではなくて実際のカラム名で行く方法もあるのですが逆にややこしくなるので数字のままにしてあります。

そしてFormat(DateAdd(“m”, 1, Date)で作成している翌月を出しています。
2024年5月に作成すれば202406となります。

ちなみに、当月を指定したい場合
pdfPath = folderPath & “\【賞与】” & employeeData(1, 1) & ” ” & employeeData(1, 2) & ” ” & Format(Date, “yyyymm”) & “.pdf”

と書き換えて、Format(Date, “yyyymm”)にすれば当月になり202405となります。

結果として
【賞与】社員番号 名前 翌月.pdf
というファイルが出来上がる仕様になっています。自由に変更してみてください。

・補足

なおエクセル上のボタンに関しては右クリックをすれば割り当てる関数を指定できるので、コピーして違う関数を作ったときはそこからどれを実行するかどうかを指定することが可能です。

ちなみにちょっとしたポイントですが、ダミーのデータを見るとE列とF列が同じ値をしています。なぜこんなことをしているかというと、エクセル上で数値として「,」が入っていてもワードに行くと消えてしまいます。
これはワードが文字列と認識するためです。
なので一度TEXT関数を利用して「,」付のデータを作る。そして、その列を反映させることでカンマ付を維持しながらワードに挿入を可能にしています。
数字などで「,」を入れたいときがあると思いますので、入らない際にはダミーデータのように別の列を作って反映させてみてください。

まとめ

いかがでしたでしょうか。
詳しい方はいつも管理しているエクセルにマクロ自体を組み込んでしまってもOKです。
もしそれが不安であればこのフォルダのままツールとして使って、作るときにデータだけコピペして使うのもありです。
データベースなどからCSVで出力するデータがあればその方が都合がいいかもしれません。

この記事を書いた人