Excelで備品購入などの記録を台帳管理している場合、見積書や注文書等の書類を作成するために、台帳の記録を見ながら作成することがあると思います。
複数の書類を作らなければならないとき、作成のたびに台帳を開いて、外注先や金額などの必要なデータを確認して書類の作成をするなど、時間と手間がかかります。
これを、ボタン1つで必要な書類が一瞬で完成されれば、かなりの時間の削減になります。
今回は購入内容管理台帳のデータから、
物品を購入するための注文書
商品を受け取った際の受領書
をそれぞれ自動作成するVBAを作成していきます。
この記事で学べる事
この記事で学べるVBAの知識は以下です。
・Excelの別ブックへのデータ入力方法
・メッセージボックスから返答を受け取る方法
・日時や金額の表示形式を変更する方法
・ファイルのコピー
・配列について
などを学びながら作成していきます。
書類作成を自動化するメリット
書類作成の自動化を行うことで、
・台帳のデータが正しければ内容を確認しなくてもミスのない書類が作成できる
・書類の作成日などの自動入力により台帳の更新の手間が省ける
・作業時間や労力が不要になる
以上のような効果が期待できます。
書類作成で使用する台帳について
［購入内容管理台帳］は以下のようになります。この台帳をもとに、各書類を自動作成していきます。業務での購入履歴は、この台帳で管理しているものとします。
［購入内容管理台帳］を更新する際に入力する列は、以下の通りです。
[注文日]、[購入先会社名]、[担当者]、[商品]、[単価]、
[数量]、[金額]、[納入期日]、［支払方法］
・注文書を提出したときに入力する列
［注文書提出日］
・受領書を提出したときに入力する列
［受領書提出日］
この台帳のデータをもとに、注文書と受領書を自動作成します。
まずは自動作成するきっかけとなるボタンを配置します。
書類出力ボタンの設置
［開発］メニューから、［挿入］をクリックし、ActiveXコントロールのボタンを選択します。
台帳の表の脇にボタンを設置します。台帳にボタンを設置したくない場合は、別シートを追加してボタンを設置しても良いです。
設置したボタンを右クリックし、プロパティを開きます。
オブジェクト名を「注文書出力ボタン」、Captionを「注文書出力」に変更します。
※日本語を入力して文字化けする場合は、メモ帳などに入力したものをコピーして、ここに張り付けてください。
オブジェクト名はVBA内で使われます。Captionはボタンの表示テキストになります。
同じように受領書出力ボタンも設置して、オブジェクト名とCaptionを変更します。
ボタンの大きさを変える場合は、［開発］メニューから［デザインモード］をクリックすると変更できるようになります。
ボタンの設置は完了です。
マクロ有効ブックとして保存
ここで一度保存しておきます。[ファイル]メニューから保存でも、Ctrl+Sのショートカットからでも保存可能です。
ファイルの種類は、「Excelマクロ有効ブック(*.xlsm)」を選択して保存します。
出力書類テンプレートの準備
各書類のフォーマットはWordかExcelかのどちらかで統一させた方が良いですが、今回は学習としてWordとExcelの2パターンでの書類出力をしていきたいと思います。
注文書テンプレート作成
注文書は、以下の無料テンプレートを参考にさせていただきました。
注文書 (商品注文書・商品売買契約書) – 無料テンプレート公開中 – 楽しもう Office (microsoft.com)
あらかじめ、データを転記する箇所（黄色囲い部分）には、わかりやすいテキスト文字を入れておきます。このテキスト文字が、どこにどの台帳データを転記するかの目印になります。
VBAでは、Wordファイルの中の指定のテキストを検索して、台帳のデータに置き換えします。
例えば、購入先会社名が「エイル株式会社」であれば、Wordファイルの中の「購入先会社名」の箇所が「エイル株式会社」に置き換えられるようVBAを作成していきます。
受領書テンプレート作成
受領書は以下のフォーマットを参考にします。
すぐに使える受領書テンプレート（Excel・PDF）無料・登録不要 | ビズ研 (biztemplatelab.com)
Wordと違い、Excelではデータを転記する際にセルの行と列を指定できるため、データを転記するセル（黄色囲い箇所）は何も入れなくて大丈夫です。
テンプレートの格納場所
テンプレートの置き場所は、台帳と同じ階層に［テンプレート］フォルダを作成し、その中に各テンプレートを格納することとします。
注文書と受領書のテンプレートを格納しました。
注文書の作成自動化
まずは、注文書の作成自動化をVBAで実装していきます。
VBA作成の流れとしては、おおまかに以下のようになります。
２．選択しているセルの行数から、台帳データを取得する
３．Wordの[注文書]テンプレートを複製し、複製ファイルに台帳データを転記する
Visual Basic Editorを開いて、VBAを入力していきます。
Visual Basic Editorについて詳しくは以下にあります↓
[注文書出力]ボタンをクリックしたら、出力確認メッセージを表示する
[注文書出力]ボタンを押したらすぐに注文書を出力するのではなく、出力をメッセージボックスで最終確認する処理を作成していきます。
クリックイベントのプロシージャ生成
プロシージャとは、処理のまとまりのことをいいます。一つの処理のかたまりをかっこよく、「プロシージャ」と言っています。
台帳の脇に設置した［注文書出力］ボタンをダブルクリックします。
Visual Basic Editorが開き、ボタンクリックイベントのプロシージャが自動生成されます。この「Private Sub~End Sub」の間に処理を書いていきます。
Visual Basic Editorについて詳しくはこちら↓
https://devoevo.com/excel-introduction/
既にVisual Basic Editorを開いている場合は、エディター上部のコンボボックスからもイベントプロシージャの生成ができます。
台帳のエディターを開き、左のコンボボックスから［注文書出力ボタン］オブジェクトを選択します。
右のコンボボックスで［Click］イベントを選択すると、イベントプロシージャが生成されます。
イベントについて詳しくはこちら↓
出力確認メッセージボックスの表示
メッセージボックスを表示させるには、MsgBox関数を使用します。
MsgBox 関数 (Visual Basic for Applications) | Microsoft Learn
このように「はい」「いいえ」ボタンが付いたメッセージボックスを表示させるには、MsgBox関数の引数（関数に渡す値）を設定します。
引数指定したMsgBox関数を使った例が以下です。「注文書作成しますか？」のメッセージの後に、［vbYesNo］という引数を指定しています。
Private Sub 注文書出力ボタン_Click()
'メッセージを表示
Dim 応答
応答 = MsgBox("注文書作成しますか？", vbYesNo)
'「はい」をクリック
If 応答 = vbYes Then
'注文書出力処理をここに記述
End If
End Subボタンの引数を付けたMsgBox関数は、ユーザーがボタンをクリックするまで待機します。変数名［応答］には、ユーザーがどのボタンをクリックしたかの情報が格納されます。
Dim 応答
応答 = MsgBox("注文書作成しますか？", vbYesNo)If文で［応答］が「はい」であるかどうかの条件判定をしています。
If 応答 = vbYes Then
'注文書出力処理をここに記述
End If
変数についてはこちら↓
If文についてはこちら↓
MsgBoxでよく使用するボタンは以下の通りです。
| vbOKOnly | OKのみ |
| vbOKCancel | OKとキャンセル |
| vbYesNo | はいといいえ |
| vbYesNoCancel | はいといいえとキャンセル |
選択しているセルの行数から、台帳データを取得する
出力確認メッセージで「はい」がクリックされたときの処理をif文内に書いていきます。
台帳のどの購入内容の注文書を作成するかは、現在選択しているセルによります。
現在選択しているセルの行数は、Selection.Row で取得できます。
'選択しているセルの行数を取得し変数に格納
Dim 選択行
選択行 = Selection.Row選択行を取得したら、注文書に出力するデータを変数に格納します。
まず注文書に出力するデータを確認します。テンプレートの黄色で囲った部分が必要なデータです。
各データを台帳から取得するため、台帳シートのオブジェクトを変数に格納します。
Dim シート
Set シート = ThisWorkbook.Worksheets("購入内容管理台帳")シートのオブジェクトは変数化しなくても問題ありませんが、何度も登場する長いオブジェクト名は、わかりやすい短縮名に変数化した方が見やすくなります。
次に、台帳の各データを格納するための変数を宣言します。
Dim 出力日, 購入先会社名, 担当者, 購入金額合計, 納入期日, 支払方法, 商品, 数量, 単価この宣言した変数に台帳から取得したデータを格納していきます。
出力日 = Format(Date, "yyyy年mm月dd日")
購入先会社名 = シート.Cells(選択行, 3).Value
担当者 = シート.Cells(選択行, 4).Value
商品 = シート.Cells(選択行, 5).Value
単価 = シート.Cells(選択行, 6).Value
数量 = シート.Cells(選択行, 7).Value
購入金額合計 = Format(シート.Cells(選択行, 8).Value, "#,###円")
納入期日 = Format(シート.Cells(選択行, 9).Value, "yyyy年mm月dd日")
支払方法 = シート.Cells(選択行, 10).Value出力日
出力日には、本日の日付を格納します。
Date関数で現在の日付を取得できますが、そのままだと「2024/10/10」のような形式になります。
Date 関数 (Visual Basic for Applications) | Microsoft Learn
そこで表示形式を変更するために、Formatという変換関数を使用します。
Format 関数 (Visual Basic for Applications) | Microsoft Learn
Format(変換対象, 変換形式)現在の日付を「2024年10月10日」という形式に変換するには以下のように書きます。
出力日 = Format(Date, "yyyy年mm月dd日")
購入先会社名、担当者、商品、単価、数量、支払方法
購入先会社名と担当者と支払方法は、台帳のC列とD列とJ列からそのまま取得します。
Cells(行数, 列数).Value でセルの値を取得できます。
購入先会社名 = シート.Cells(選択行, 3).Value 担当者 = シート.Cells(選択行, 4).Value 商品 = シート.Cells(選択行, 5).Value 単価 = シート.Cells(選択行, 6).Value 数量 = シート.Cells(選択行, 7).Value 支払方法 = シート.Cells(選択行, 10).Value
購入金額合計
購入金額合計は、セルからそのまま取得すると「2000」のようにただの整数となるので、「2,000円」となるようFormat関数を使って変換します。
購入金額合計 = Format(シート.Cells(選択行, 8).Value, "#,###円")
納入期日
納入期日も出力日と同様、Format関数で変換します。日付データは台帳のI列から取得します。
納入期日 = Format(シート.Cells(選択行, 9).Value, "yyyy年mm月dd日")
ここまでのコードを以下にまとめます。
Private Sub 注文書出力ボタン_Click()
'メッセージを表示
Dim 応答
応答 = MsgBox("注文書作成しますか？", vbYesNo)
'「はい」をクリック
If 応答 = vbYes Then
'選択しているセルの行数を取得し変数に格納
Dim 選択行
選択行 = Selection.Row
'シートオブジェクトの変数作成
Dim シート
Set シート = ThisWorkbook.Worksheets("購入内容管理台帳")
'各要素格納
Dim 出力日, 購入先会社名, 担当者, 購入金額合計, 納入期日, 支払方法, 商品, 数量, 単価
出力日 = Format(Date, "yyyy年mm月dd日")
購入先会社名 = シート.Cells(選択行, 3).Value
担当者 = シート.Cells(選択行, 4).Value
商品 = シート.Cells(選択行, 5).Value
単価 = シート.Cells(選択行, 6).Value
数量 = シート.Cells(選択行, 7).Value
購入金額合計 = Format(シート.Cells(選択行, 8).Value, "#,###円")
納入期日 = Format(シート.Cells(選択行, 9).Value, "yyyy年mm月dd日")
支払方法 = シート.Cells(選択行, 10).Value
End If
End Sub 
Wordの[注文書]テンプレートを複製し、複製ファイルに台帳データを転記する
［注文書］テンプレートを複製
ファイルの複製にはFileCopy関数を使用します。
FileCopy ステートメント (VBA) | Microsoft Learn
FileCopy “コピー元ファイルパス”, “コピー先ファイルパス”コピー元は、Excel台帳と同じフォルダに作成した[テンプレート]フォルダに格納したテンプレートのファイルパスを指定します。
コピー先は、Excel台帳と同じフォルダに同名ファイル名のパスを指定します。
FileCopy ThisWorkbook.Path & "\テンプレート\注文書.docx", ThisWorkbook.Path & "\注文書.docx"※コピー元ファイルが開いていると複製できずにエラーになります。
Wordファイルの操作の準備
ExcelVBAで関数を使ってExcel操作するように、Wordの操作もWord操作用の関数を使用します。
しかし、Excel VBAからそのままWordの関数は使用できません。使用するにはまずCreateObject関数を使って、Wordオブジェクトの作成をする必要があります。
Dim ワード
Set ワード = CreateObject("Word.Application")このWordオブジェクトを作成することで、Wordの関数を使用してWordファイル内の操作をすることができます。
作成したら先ほど複製した［注文書］ファイルを開きます。
Wordファイルを開くには、Documents.Open関数を使用してファイルパスを指定します。
Dim 注文書
Set 注文書 = ワード.Documents.Open(ThisWorkbook.Path & "\注文書.docx")
Wordの終了処理
WordをVBAから開いたら必ず閉じる処理もセットで記述します。
'保存して閉じる
注文書.Save
注文書.Close
'Wordアプリケーションを終了
ワード.Quit
このようなエラーを起こさないようにコードを書くことが重要ですが、もし上記のようなエラーが出たら、タスクスケジューラからWordのタスクを強制終了させると解消されます。
※ただし、せっかく作成したVBAファイルが正常に開くかは保障できません。
ここまでのVBAコードを以下にまとめます。
Private Sub 注文書出力ボタン_Click()
'メッセージを表示
Dim 応答
応答 = MsgBox("注文書作成しますか？", vbYesNo)
'「はい」をクリック
If 応答 = vbYes Then
'選択しているセルの行数を取得し変数に格納
Dim 選択行
選択行 = Selection.Row
'シートオブジェクトの変数作成
Dim シート
Set シート = ThisWorkbook.Worksheets("購入内容管理台帳")
'各要素格納
Dim 出力日, 購入先会社名, 担当者, 購入金額合計, 納入期日, 支払方法, 商品, 数量, 単価
出力日 = Format(Date, "yyyy年mm月dd日")
購入先会社名 = シート.Cells(選択行, 3).Value
担当者 = シート.Cells(選択行, 4).Value
商品 = シート.Cells(選択行, 5).Value
単価 = シート.Cells(選択行, 6).Value
数量 = シート.Cells(選択行, 7).Value
購入金額合計 = Format(シート.Cells(選択行, 8).Value, "#,###円")
納入期日 = Format(シート.Cells(選択行, 9).Value, "yyyy年mm月dd日")
支払方法 = シート.Cells(選択行, 10).Value
'テンプレートをコピー
FileCopy ThisWorkbook.Path & "\テンプレート\注文書.docx", ThisWorkbook.Path & "\注文書.docx"
'Wordオブジェクトを作成し複製したファイルを開く
Dim ワード
Set ワード = CreateObject("Word.Application")
'複製したファイルを開く
Dim 注文書
Set 注文書 = ワード.Documents.Open(ThisWorkbook.Path & "\注文書.docx")
'ここにWord操作の処理を書きます
'保存して閉じる
注文書.Save
注文書.Close
' Wordアプリケーションを終了
ワード.Quit
End If
End SubこれでWordを操作する準備が整いました。あとは、Wordの関数やプロパティを使って、注文書ファイルの操作をするだけです。
Wordファイルから文字列を検索し台帳データに置き換える
Excelファイルの操作では、セルに文字を入力するときは以下のようにセルの行数と列数を指定すればできます。
ThisWorkbook.Worksheets("シート名").Cells(行数, 列数).Value = “あいうえお”しかし、Wordファイルでは文書を作成する場合、セルのように行数と列数を指定して値を入力することはできません。
そこで、Wordでは既に入力してある文字を起点にして、別の文字に置き換えるという方法があります。
Wordによる文字の置換
エクセルVBAから行う、Wordの機能を使った文字の置換の流れは以下です。
② 検索した文字を指定した置換文字に置き換える
始めに［注文書］ファイルの右上、「出力日」から置き換えていきます。
以下の部分です。
文字の検索と置換には、.Selection.Findオブジェクトを使用します。
Wordの[検索と置換] ダイアログ ボックスのオプションと対応します。
Find.Textに検索する文字を設定します。
Find.Text プロパティ (Word) | Microsoft Learn
ワード.Selection.Find.Text = "出力日"
文字の置換にはExecuteを使用します。
Find.Execute メソッド (Microsoft.Office.Interop.Word) | Microsoft Learn
ワード.Selection.Find.Execute Replacewith:=出力日, Replace:=2引数のReplacewithには、置換する文字を指定します。
引数のReplaceには、２（すべて置換する）、１（最初に一致したものを置換する）、０（置換を行わない）の中の２を指定します。
Find.ClearFormattingで検索条件から書式をクリアします。これは念のためです。
Find.ClearFormatting メソッド (Word) | Microsoft Learn
ワード.Selection.Find.ClearFormatting以上が文字の置換の一連の流れです。
同じように出力日以外も文字を置換していきます。
コード全体です。
Private Sub 注文書出力ボタン_Click()
'メッセージを表示
Dim 応答
応答 = MsgBox("注文書作成しますか？", vbYesNo)
'「はい」をクリック
If 応答 = vbYes Then
'選択しているセルの行数を取得し変数に格納
Dim 選択行
選択行 = Selection.Row
'シートオブジェクトの変数作成
Dim シート
Set シート = ThisWorkbook.Worksheets("購入内容管理台帳")
'各要素格納
Dim 出力日, 購入先会社名, 担当者, 購入金額合計, 納入期日, 支払方法, 商品, 数量, 単価
出力日 = Format(Date, "yyyy年mm月dd日")
購入先会社名 = シート.Cells(選択行, 3).Value
担当者 = シート.Cells(選択行, 4).Value
商品 = シート.Cells(選択行, 5).Value
単価 = シート.Cells(選択行, 6).Value
数量 = シート.Cells(選択行, 7).Value
購入金額合計 = Format(シート.Cells(選択行, 8).Value, "#,###円")
納入期日 = Format(シート.Cells(選択行, 9).Value, "yyyy年mm月dd日")
支払方法 = シート.Cells(選択行, 10).Value
'テンプレートをコピー
FileCopy ThisWorkbook.Path & "\テンプレート\注文書.docx", ThisWorkbook.Path & "\注文書.docx"
'Wordオブジェクトを作成し複製したファイルを開く
Dim ワード
Set ワード = CreateObject("Word.Application")
'複製したファイルを開く
Dim 注文書
Set 注文書 = ワード.Documents.Open(ThisWorkbook.Path & "\注文書.docx")
'出力日
ワード.Selection.Find.Text = "出力日"
ワード.Selection.Find.Execute Replacewith:=出力日, Replace:=2
ワード.Selection.Find.ClearFormatting
'購入先会社名
ワード.Selection.Find.Text = "購入先会社名"
ワード.Selection.Find.Execute Replacewith:=購入先会社名, Replace:=2
ワード.Selection.Find.ClearFormatting
'担当者
ワード.Selection.Find.Text = "担当者"
ワード.Selection.Find.Execute Replacewith:=担当者, Replace:=2
ワード.Selection.Find.ClearFormatting
'購入金額合計
ワード.Selection.Find.Text = "購入金額合計"
ワード.Selection.Find.Execute Replacewith:=購入金額合計, Replace:=2
ワード.Selection.Find.ClearFormatting
'納入期日
ワード.Selection.Find.Text = "納入期日"
ワード.Selection.Find.Execute Replacewith:=納入期日, Replace:=2
ワード.Selection.Find.ClearFormatting
'支払方法
ワード.Selection.Find.Text = "支払方法"
ワード.Selection.Find.Execute Replacewith:=支払方法, Replace:=2
ワード.Selection.Find.ClearFormatting
'保存して閉じる
注文書.Save
注文書.Close
' Wordアプリケーションを終了
ワード.Quit
End If
End Sub商品名、数量、単価の入力はまだですが、ここで一旦テストします。
どの列でも良いので、台帳のセルをクリックし、［注文書出力］ボタンをクリックします。
出力確認メッセージが出ました。［はい］ボタンを押します。
少しすると、同階層に注文書が作成されているのが確認できます。開くとちゃんと文字が台帳のデータに置き換えられていることが確認できます。
Wordファイルのテーブルに台帳データを入力する
［注文書］ファイルには、注文内容を入力するテーブルが設置されています。
ここにも台帳データを入力していきます。
テーブルに値を入力するのは簡単で、Excelのようにセルの指定をすれば良いです。
注文書.tables(テーブル番号).cell(行数, 列数).Range.Text = 台帳データテーブル番号には、テーブルが複数の場合は作成されたテーブルの順に番号が入ります。今回は1つしか作成されていないので、.tables (1)となります。
'テーブルに値を入力
注文書.tables(1).cell(2, 1).Range.Text = 商品
注文書.tables(1).cell(2, 2).Range.Text = 数量
注文書.tables(1).cell(2, 3).Range.Text = 単価前もって変数に格納していた、台帳データの商品と数量と単価をテーブルに格納しています。
再びテストしてみて、正しくテーブルにデータが入力されるか確認します。
ちゃんとテーブルに台帳データが入力されました。
補助機能の追加
出力完了のメッセージを表示する
これまで作成した注文書出力処理では、正常に処理が完了したかは、ユーザーが注文書の格納フォルダを見に行くことでしか確認できません。
そこで、正常終了したらメッセージを表示してユーザーに書類作成完了をお知らせする処理を追記します。
場所は、注文書出力の処理が終わった End If の直前に追記します。
MsgBox "出力が完了しました"
End If
注文書提出日を台帳に入力
進捗管理で注文書提出日を台帳に入力して管理しているのならば、それもVBAの中で入力してしまえば手間が省けます。
注文書提出日＝注文書出力日として、以下のように現在の日付を入力します。これも出力処理の最後の方に作成します。
'台帳に提出日を追記
シート.Cells(選択行, 11) = Date
台帳のセルが選択されていないときの警告メッセージ表示
注文書出力処理では、ユーザーが出力したい台帳のセルをクリックして［注文書出力］ボタンを押す必要があります。
もし、以下のようにユーザー台帳の表以外のセルを選択して、［注文書出力］ボタンを押すとどうなるでしょうか？
このコードではエラーは出ませんが、選択した台帳データが空であるため、出力した注文書には必要な情報が入力されずに作成されてしまいます。
台帳内のセルが選択されていない場合、ユーザーに以下のような警告をして注文書出力処理を中断する必要があります。
そこで、［注文書出力］ボタンクリックのイベント発生時に、台帳内のセルを選択しているか確認する処理を追記します。
台帳内のセルがクリックされているか、されていないかの条件判定は、IF文にすると以下のように書くことができます。
If ActiveCell.Value = "" Then
MsgBox "台帳内のセルを選択してからボタンをクリックしてください"
Exit Sub
End IfActiveCell.Valueで、選択しているセルの値を取得できます。
そして、セルの値が空欄だった場合は、メッセージで警告してイベント処理を終了します。
Exit Subは、処理を途中で抜けたい場合に使用します。このExit Subが無ければ、if文を抜けた後もそのまま処理を続行してしまいます。
ボタンクリックイベントに組み込むと以下のようになります。
Private Sub 注文書出力ボタン_Click()
'セルにデータが入っていなければメッセージを表示する処理
If ActiveCell.Value = "" Then
MsgBox "台帳内のセルを選択してからボタンをクリックしてください"
Exit Sub
End If
'メッセージを表示
Dim 応答
応答 = MsgBox("注文書作成しますか？", vbYesNo)
'「はい」をクリック
If 応答 = vbYes Then
・・・・・・・・わざと台帳の表以外のセルを選択して［注文書出力］ボタンをクリックして、警告メッセージが表示されるか確認してみましょう。
コード量を短くする方法
以下の部分ですが、Wordファイルへの検索と置換という処理が何度も行われています。
'出力日
ワード.Selection.Find.Text = "出力日"
ワード.Selection.Find.Execute Replacewith:=出力日, Replace:=2
ワード.Selection.Find.ClearFormatting
'購入先会社名
ワード.Selection.Find.Text = "購入先会社名"
ワード.Selection.Find.Execute Replacewith:=購入先会社名, Replace:=2
ワード.Selection.Find.ClearFormatting
'担当者
ワード.Selection.Find.Text = "担当者"
ワード.Selection.Find.Execute Replacewith:=担当者, Replace:=2
ワード.Selection.Find.ClearFormatting
'購入金額合計
ワード.Selection.Find.Text = "購入金額合計"
ワード.Selection.Find.Execute Replacewith:=購入金額合計, Replace:=2
ワード.Selection.Find.ClearFormatting
'納入期日
ワード.Selection.Find.Text = "納入期日"
ワード.Selection.Find.Execute Replacewith:=納入期日, Replace:=2
ワード.Selection.Find.ClearFormatting
'支払方法
ワード.Selection.Find.Text = "支払方法"
ワード.Selection.Find.Execute Replacewith:=支払方法, Replace:=2
ワード.Selection.Find.ClearFormattingこのように何度も同じ処理を行う場合は、一個の処理にまとめて書く方が良いです。
まとめて書かない場合、検索と置換の項目を追加したい場合には、同じ処理をそのたびに書かなければいけなくなり、だらだらと長いコードになってしまいます。
繰り返し同じ処理を行う場合に使用するのはFor文です。
For文について詳しくはこちら↓
For文を使えば以下のように書くことができます。
Dim i
For i = 0 To 検索文字の配列数
ワード.Selection.Find.ClearFormatting
ワード.Selection.Find.Text = 検索文字の配列(i)
ワード.Selection.Find.Execute Replacewith:=置換文字の配列(i), Replace:=2
NextFor文の中で検索文字と置換文字を指定するには、配列を使用します。
配列とは
配列とは、データが入った変数の列車のようなものです。
変数に格納できる値が1つですが、配列には値を複数格納することができます。
変数の値を利用する場合は、変数名を記述すれば良いですが、配列は配列名にカッコ(数字)を付けて配列の前から何番目かを指定します。
以下は配列名に[検索文字]と命名した配列の、値の指定方法です。
0番目の値は[検索文字(0)]と指定することで取得できます。
配列は変数と同じく、最初に宣言をしてから値を格納します。検索文字と置換文字の配列を作成し、値を格納するには以下のように書きます。
'配列の変数に格納
Dim 検索文字(), 置換文字()
検索文字 = Array("出力日", "購入先会社名", "担当者", "購入金額合計", "納入期日", "支払方法")
置換文字 = Array(出力日, 購入先会社名, 担当者, 購入金額合計, 納入期日, 支払方法)配列の宣言は、配列名にカッコ（）を付けるだけです。これで配列を使うという宣言になります。
配列を使う際は、For文でカウント変数を配列の番号に使い、配列の値を順に指定しながら繰り返し処理を行うことができます。
'Wordファイル内の文字列を置換
Dim i
For i = 0 To UBound(検索文字)
ワード.Selection.Find.ClearFormatting
ワード.Selection.Find.Text = 検索文字(i)
ワード.Selection.Find.Execute Replacewith:=置換文字(i), Replace:=2
NextUBoundは配列の大きさを取得する関数です。配列の値が入っている個数分、処理を繰り返します。
UBound 関数 (Visual Basic for Applications) | Microsoft Learn
コード全体です。だいぶすっきりしましたね。
Private Sub 注文書出力ボタン_Click()
'セルにデータが入っていなければメッセージを表示する処理
If ActiveCell.Value = "" Then
MsgBox "台帳内のセルを選択してからボタンをクリックしてください"
Exit Sub
End If
'メッセージを表示
Dim 応答
応答 = MsgBox("注文書作成しますか？", vbYesNo)
'「はい」をクリック
If 応答 = vbYes Then
'シートを短縮名に変数化
Dim シート
Set シート = ThisWorkbook.Worksheets("購入内容管理台帳")
'選択している行を取得
Dim 選択行
選択行 = Selection.Row
'各要素格納
Dim 出力日, 購入先会社名, 担当者, 購入金額合計, 納入期日, 支払方法, 商品, 数量, 単価
出力日 = Format(Date, "yyyy年mm月dd日")
購入先会社名 = シート.Cells(選択行, 3).Value
担当者 = シート.Cells(選択行, 4).Value
商品 = シート.Cells(選択行, 5).Value
単価 = シート.Cells(選択行, 6).Value
数量 = シート.Cells(選択行, 7).Value
購入金額合計 = Format(シート.Cells(選択行, 8).Value, "#,###円")
納入期日 = Format(シート.Cells(選択行, 9).Value, "yyyy年mm月dd日")
支払方法 = シート.Cells(選択行, 10).Value
'テンプレートをコピー
FileCopy ThisWorkbook.Path & "\テンプレート\注文書.docx", ThisWorkbook.Path & "\注文書.docx"
'Wordオブジェクトを作成し複製したファイルを開く
Dim ワード
Set ワード = CreateObject("Word.Application")
'複製したファイルを開く
Dim 注文書
Set 注文書 = ワード.Documents.Open(ThisWorkbook.Path & "\注文書.docx")
'配列の変数に格納
Dim 検索文字(), 置換文字()
検索文字 = Array("出力日", "購入先会社名", "担当者", "購入金額合計", "納入期日", "支払方法")
置換文字 = Array(出力日, 購入先会社名, 担当者, 購入金額合計, 納入期日, 支払方法)
'Wordファイル内の文字列を置換
Dim i
For i = 0 To UBound(検索文字)
ワード.Selection.Find.ClearFormatting
ワード.Selection.Find.Text = 検索文字(i)
ワード.Selection.Find.Execute Replacewith:=置換文字(i), Replace:=2
Next
'テーブルに値を入力
注文書.tables(1).cell(2, 1).Range.Text = 商品
注文書.tables(1).cell(2, 2).Range.Text = 数量
注文書.tables(1).cell(2, 3).Range.Text = 単価
'保存して閉じる
注文書.Save
注文書.Close
' Wordアプリケーションを終了
ワード.Quit
'台帳に提出日を追記
シート.Cells(選択行, 11) = Date
MsgBox "出力が完了しました"
End If
End Sub
受領書の出力自動化
［受領書出力］ボタンをクリックした際に、受領書テンプレートを複製し、複製ファイルに台帳のデータを転記するVBAを実装していきます。
VBA作成の流れとしては、さきほどの注文書出力と同じです。
２．選択しているセルの行数から、台帳データを取得する
３．Excelの[受領書]テンプレートを複製し、複製ファイルに台帳データを転記する
[受領書出力]ボタンをクリックしたら、出力確認メッセージを表示する
注文書同様、出力をメッセージボックスで最終確認する処理を作成していきます。
エディターに[受領書出力]ボタンのクリックイベントを追加します。
Private Sub 注文書出力ボタン_Click()
'セルにデータが入っていなければメッセージを表示する処理
If ActiveCell.Value = "" Then
MsgBox "台帳内のセルを選択してからボタンをクリックしてください"
Exit Sub
End If
'メッセージを表示
Dim 応答
応答 = MsgBox("受領書作成しますか？", vbYesNo)
'「はい」をクリック
If 応答 = vbYes Then
‘受領書テンプレートに転記する処理
End If
End Sub[注文書出力]ボタンクリックイベントと同様、出力確認メッセージ表示の前には、台帳データが選択されているかどうかの確認処理を入れます。
選択しているセルの行数から、台帳データを取得する
こちらも注文書出力処理と同じように処理を追記していきます。必要なデータは以下テンプレートの黄色背景の部分です。
変数を宣言し、台帳データを格納します。
'各要素格納
Dim 出力日, 購入先会社名, 担当者,商品, 数量
出力日 = Format(Date, "yyyy年mm月dd日")
購入先会社名 = シート.Cells(選択行, 3).Value
担当者 = シート.Cells(選択行, 4).Value
商品 = シート.Cells(選択行, 5).Value
数量 = シート.Cells(選択行, 7).Value
Excelの[受領書]テンプレートを複製し、複製ファイルに台帳データを転記する
ExcelVBAから別ブックのExcelファイルにデータ入力する処理は、先ほど作成したWordへのデータ入力よりも簡単です。
まず、受領書のテンプレートを複製します。
ここは先ほどのWordテンプレートの複製と同じです。ファイル名を受領書ファイルに変えただけです。
'テンプレートをコピー
FileCopy ThisWorkbook.Path & "\テンプレート\受領書.xlsx", ThisWorkbook.Path & "\受領書.xlsx"その後、複製した受領書ファイルを開きます。
'複製したファイルを開く
Workbooks.Open (ThisWorkbook.Path & "\受領書.xlsx")そして、受領書のブックと書き込むシートを指定してセルに入力するだけです。
'台帳データを受領書に代入する
With Workbooks("受領書.xlsx").Sheets("Sheet1")
.Range("H5").Value = 出力日
.Range("B7").Value = 購入先会社名
.Range("B8").Value = 担当者
.Range("B15").Value = 商品
.Range("F15").Value = 数量
End With［With～End With］を使ってシートの指定部分のオブジェクトを省略しています。
受領書のセルの指定は .Range(セル範囲) を使っていますが、.Cells(行数, 列数)で指定してもいいです。
最後に台帳に受領書提出日の入力と、出力完了メッセージ表示を書いたら完成です。
以下がコード全体です。
Private Sub 受領書出力ボタン_Click()
'セルにデータが入っていなければメッセージを表示する処理
If ActiveCell.Value = "" Then
MsgBox "台帳内のセルを選択してからボタンをクリックしてください"
Exit Sub
End If
'メッセージを表示
Dim 応答
応答 = MsgBox("受領書作成しますか？", vbYesNo)
'「はい」をクリック
If 応答 = vbYes Then
'選択しているセルの行数を取得し変数に格納
Dim 選択行
選択行 = Selection.Row
'シートオブジェクトの変数作成
Dim シート
Set シート = ThisWorkbook.Worksheets("購入内容管理台帳")
'各要素格納
Dim 出力日, 購入先会社名, 担当者, 商品, 数量
出力日 = Format(Date, "yyyy年mm月dd日")
購入先会社名 = シート.Cells(選択行, 3).Value
担当者 = シート.Cells(選択行, 4).Value
商品 = シート.Cells(選択行, 5).Value
数量 = シート.Cells(選択行, 7).Value
'テンプレートをコピー
FileCopy ThisWorkbook.Path & "\テンプレート\受領書.xlsx", ThisWorkbook.Path & "\受領書.xlsx"
'複製したファイルを開く
Workbooks.Open (ThisWorkbook.Path & "\受領書.xlsx")
'台帳データを受領書に代入する
With Workbooks("受領書.xlsx").Sheets("Sheet1")
.Range("H5").Value = 出力日
.Range("B7").Value = 購入先会社名
.Range("B8").Value = 担当者
.Range("B15").Value = 商品
.Range("F15").Value = 数量
End With
'台帳に提出日を追記
シート.Cells(選択行, 12) = Date
MsgBox "出力が完了しました"
End If
End Subテストします。
[受領書出力]ボタンをクリックし、ファイルが出力されるか確認しましょう。
ちゃんと出力されたようです。
受領書提出日の入力もできています。（受領書提出日＝受領書出力日としています）
まとめ
今回は購入内容管理台帳のデータから、注文書と受領書の自動作成をするVBAを実装しました。
別ファイルへのデータ入力は、Wordへは文字の置換、Excelはセルを指定し格納でできます。
一度作成してしまえば、他の書類出力にも転用できるので、書類作成作業をすぐに自動化できて便利です。
