【VBA】【請求書自動印刷システム】3日目(最終日) 品目部分の移し替え

2022.10.10更新

【VBA】【請求書自動印刷システム】のタイトル画像になります

ExcelVBAを使って請求書一覧からデータを読み取り一気に印刷する自動印刷システムを作っていきます。

請求書自動印刷システム作りを通してExcelVBAを使用する前提でのマスターデータの作り方、大量なデータの一括処理方法、その時々によって個数が変化するデータの取り扱い方などを学んでいきます。

「ExcelVBAでプログラミング入門」の応用編になりますので、まだ終えてない方はそちらを先にご覧ください。

請求書自動印刷システム

最終日です！

あらためまして以下のデータを使って請求書自動印刷システムを作っていきます。

プログラムの全体については完成ソースをご覧ください。

シート名「請求先データ」

シート名「単価一覧」

シート名「請求書」

移し替える先のセルになります。

会社名→シート請求書A5セル

郵便番号→シート請求書A8セル

住所１→シート請求書A9セル

住所２→シート請求書A10セル

そしてシート請求書のA19～G21の間に数量がある品目だけを記載するようにしていきます。

それでは実際にやっていきましょう！

必要な変数

まずは変数の説明をします。

			
'グローバル変数
'「請求先データ」の行数カウント用
Dim data_gyou_count As Integer
'「請求先データ」のデータの列数カウント用
Dim data_retu_count As Integer
'品目の単価用
Dim tanka As Integer

品目の単価を調べる関数を別に作るので、いくつかの変数をどの関数からでも使えるようにグローバル変数として宣言しておきます。


'ローカル変数
'「請求書」の品目欄のカウント用
Dim hinmoku_gyou_count As Integer

あとはローカル変数としてこちらを用意します。

変数初期化

次の値で初期化します。


'変数初期化
data_gyou_count = 2
data_retu_count = 5
hinmoku_gyou_count = 19

「data_gyou_count」→題名を抜いた２行目からカウントするので「2」

「data_retu_count」→品目の先頭「基本料金」からカウントするので「5」

「hinmoku_gyou_count」→「請求書」A19から品目欄が始まるので「19」

みたいな感じになります。

データの移し替え

それでは前回と同じようにデータの移し替えを行っていきます。


Do While Worksheets("請求先データ").Cells(data_gyou_count, 1).Value <> ""

	'会社名
	Worksheets("請求書").Range("A5").Value = _ 
		Worksheets("請求先データ").Cells(data_gyou_count, 1).Value

	'郵便番号
	Worksheets("請求書").Range("A8").Value = _ 
		"〒" & Worksheets("請求先データ").Cells(data_gyou_count, 2).Value

	'住所１
	Worksheets("請求書").Range("A9").Value = _
		Worksheets("請求先データ").Cells(data_gyou_count, 3).Value

	'住所２
	Worksheets("請求書").Range("A10").Value = _ 
		Worksheets("請求先データ").Cells(data_gyou_count, 4).Value

	'印刷
	Worksheets("請求書").PrintOut

	'書き込んだデータを消去
	Worksheets("請求書").Range("A5").Value = ""
	Worksheets("請求書").Range("A8").Value = ""
	Worksheets("請求書").Range("A9").Value = ""
	Worksheets("請求書").Range("A10").Value = ""
	Worksheets("請求書").Range("A19:G21").Value = ""

	'行を一つ進める
	data_gyou_count = data_gyou_count + 1

	'「会社名」が「計」まで行ったらループを抜ける
	If Worksheets("請求先データ").Cells(data_gyou_count, 1).Value = "計" Then
		Exit Do
	End If

Loop

ほとんど前回と同じでただデータを移しているだけなので詳しい説明は省略します。

前のデータが残らないようにいちよう毎回書き込んだデータを消去しております。

品目の移し替え

今回のメインテーマ、品目部分の移し替えを行っていきます。

基本のループ処理

			
'「請求額」項目になるまで品目データを移し替え(品目の増加・減少に備えて)
Do While Worksheets("請求先データ").Cells(1, data_retu_count).Value <> "請求額"

	'品目移し替え作業

Loop

基本のループ処理です。

ポイントは「請求先データ」の項目の一番最後「請求額」になるまで繰り返しをするというトコロです。

これをする事によって新たな品目の追加、削除などができるようになります。

品目ごとの単価を調べる


'シート「単価一覧」から品目ごとの単価を選び出す
Sub my_choose_tanka()
    Dim i As Integer
    
    i = 2
    
    Do While Worksheets("単価一覧").Cells(i, 1).Value <> ""
        If Worksheets("単価一覧").Cells(i, 1).Value = Worksheets("請求先データ").Cells(1, data_retu_count).Value Then
            tanka = Worksheets("単価一覧").Cells(i, 2).Value
            Exit Do
        Else
            tanka = 0
        End If
        
        i = i + 1
    Loop
End Sub

シート「単価一覧」から品目ごとの単価を選び出す関数「my_choose_tanka」になります。

基本的な考え方は同じです。

シート「単価一覧」の品目名が空白になるまで繰り返し、その中で品目名が一致したものがあればその単価を変数「tanka」に保存という流れになります。

通常は品目それぞれにコード番号などを割り振って管理するのが普通ですが、説明がわかりやすいように今回は直接品目名で調べる方法をとりました。

「請求先データ」と「単価一覧」で品目名を一字一句間違わずに登録するトコロに注意です。

品目名が見当たらなかった時は「tanka = 0」にするエラー対策をとっております。

数量がある時だけ品目を移し替え


'品目ごとの単価を選び出す
Call my_choose_tanka
            
'各品目、数量が「1」以上の時だけデータを移し替える
If Worksheets("請求先データ").Cells(data_gyou_count, data_retu_count).Value > 0 Then
                
	'品目名
	Worksheets("請求書").Cells(hinmoku_gyou_count, 1).Value = _
		Worksheets("請求先データ").Cells(1, data_retu_count).Value

	'品目ごとの単価
	Worksheets("請求書").Cells(hinmoku_gyou_count, 4).Value = tanka

	'数量
	Worksheets("請求書").Cells(hinmoku_gyou_count, 6).Value = _
		Worksheets("請求先データ").Cells(data_gyou_count, data_retu_count).Value

	'価格
	Worksheets("請求書").Cells(hinmoku_gyou_count, 7).Value = _
		tanka * Worksheets("請求先データ").Cells(data_gyou_count, data_retu_count).Value

	hinmoku_gyou_count = hinmoku_gyou_count + 1

End If
            
data_retu_count = data_retu_count + 1

あとは品目の数量がある時だけ移し替えするように「If」文を加えて「hinmoku_gyou_count」をその時だけ進めるようにすれば品目部分を可変にする事ができますね！