photo credit: Bombón en la Bombonera via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
脱エクセルVBA初心者向け、請求書を自動で作る際の様々なテクニックについて連載でお伝えしてきました。
前回の記事はコチラ。
Vlookup時に発生するエラーを上手に回避する方法についてお伝えしました。
これにて、請求書作成プログラムはだいぶ出来上がってきまして、残す処理はほんの少しとなりました。
過去の記事でお伝えしたテクニックも多く含まれますが、おさらいも含めまして総仕上げの解説をしていきます。
前回までのおさらい
前回までで作成したプログラムはこちらです。
Sub 請求書作成()
Dim wsData As Worksheet '「請求データ」シート
Set wsData = ThisWorkbook.Worksheets("請求データ")
Dim rngAccount As Range '「取引先マスタ」検索範囲
Set rngAccount = ThisWorkbook.Worksheets("取引先マスタ").Range("A:D")
Call wsDataSort
Dim startRow As Long 'コピー範囲の最初の行と最終行を格納
startRow = 2
Dim wsInvoice As Worksheet '「請求書ひな形」シート
Dim strFile As String '保存先フォルダパス＆ファイル名（拡張子抜き）
Dim strAccount As String '取引先名
Dim i As Long
i = 2
Do While wsData.Cells(i, 1).Value <> ""
i = i + 1
If firstDay(wsData.Cells(i, 1).Value) <> firstDay(wsData.Cells(startRow, 1).Value) Or _
wsData.Cells(i, 2).Value <> wsData.Cells(startRow, 2).Value Then
'*****新規ワークブックを作成*****
Workbooks.Add '新規ワークブックを作成
ThisWorkbook.Worksheets("請求書ひな形").Copy before:=ActiveWorkbook.Sheets(1) '新規ワークブックのsheet1の前にひな形をコピー
Set wsInvoice = ActiveSheet 'コピーしたシートを変数にセット
wsInvoice.Name = "請求書" 'シート名を変更
Application.DisplayAlerts = False '確認メッセージをオフにする
ActiveWorkbook.Worksheets("Sheet1").Delete 'Sheet1を削除する
Application.DisplayAlerts = True '確認メッセージをオンにする
strFile = ThisWorkbook.Path & "\" & Format(wsData.Cells(startRow, 1).Value, "YYYYMM") & "請求書_" & wsData.Cells(startRow, 2).Value & "御中"
'*****PDF出力設定*****
With wsInvoice.PageSetup
.Zoom = False '倍率をクリア
.FitToPagesWide = 1 '横方向に1ページに収める
.FitToPagesTall = 1 '縦方向に1ページに収める
.CenterHorizontally = True '水平方向に中央配置
.TopMargin = Application.CentimetersToPoints(1) '上マージンを1cm
.BottomMargin = Application.CentimetersToPoints(1) '下マージンを1cm
End With
'*****請求書の各データを入力*****
wsData.Range(wsData.Cells(startRow, 3), wsData.Cells(i - 1, 5)).Copy wsInvoice.Range("A21") '範囲をコピペ
On Error Resume Next 'エラーを無視
strAccount = wsData.Cells(startRow, 2).Value '取引先名
wsInvoice.Range("A3").Value = strAccount & " 御中" '取引先名
wsInvoice.Range("A5").Value = "〒" & WorksheetFunction.VLookup(strAccount, rngAccount, 2, False) '郵便番号
wsInvoice.Range("A6").Value = WorksheetFunction.VLookup(strAccount, rngAccount, 3, False) '住所1
wsInvoice.Range("A7").Value = WorksheetFunction.VLookup(strAccount, rngAccount, 4, False) '住所2
If Err.Number <> 0 Then 'エラーが発生したときにメッセージを表示
MsgBox strAccount & "の情報を取得するVlookupでエラーが発生しました"
Err.Clear 'Errオブジェクトをクリア
End If
'*****PDF出力をして保存して閉じる*****
wsInvoice.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFile & ".pdf" '選択したシートをPDF出力
ActiveWorkbook.Close savechanges:=True, Filename:=strFile & ".xlsx" 'アクティブブックを名前を付けて保存して閉じる
startRow = i
End If
Loop
End Sub
請求データを年月×取引先の範囲ごとにまとめる
「請求データ」シートはこちら。
まずこのシートについて、wsDataSortで並び替えをします。
Sub wsDataSort()
With ActiveWorkbook.Worksheets("請求データ")
'請求データをA列納品日、B列取引先で並び替え（見出しは範囲に含めない）
.Range("A1").CurrentRegion.Sort Key1:=.Range("B2"), Key2:=.Range("A2"), Header:=xlYes
End With
End Sub
並び替えをした、「請求データ」シートを1行目から走査して、同じ納品月、同じ取引先である範囲をひとまとまりとします。
その判定を行っているIf文が24行目です。firstDayは日付を1日に揃える関数でこちらです。
Function firstDay(ByVal d As Date) As Date
firstDay = DateSerial(Year(d), Month(d), 1)
End Function
年月×取引先ごとにワークブックを作成する
範囲のまとまりごとに請求書を作成（つまり、年月×取引先ごと）しますが、個々のワークブックを新規作成しシート等の準備をしている箇所が28行目～38行目です。
こちらの「請求書ひな形」シートをコピーしてワークブックを作成します。
PDF出力の設定と実行
41行目～50行目でPDFの出力に関する設定を行い、69行目で実際に出力をしています。
請求データを範囲でコピー＆ペースト
行目はたった1行ですが、対象とする範囲をまるっとコピーして、新しく作ったワークブックにペーストをする処理です。
Vlookupで取引先情報の抽出とエラー処理
57行目～61行目で以下の「取引先マスタ」シートから各種情報を抽出してきて、請求書に転記をします。
Vlookupで目的のキーワードでヒットしない場合はエラーになってしまうので、それを回避するのが55行目の処理となります。
今回のお題：請求書を整えていく
上記プログラムを実行して出力されるPDFはこのようになります。
まだいくつか足りない部分がありますね。
- 価格列と請求額金額総額が計算されていない
- 請求日・支払期限が正しくない
- 余計な行がたくさん含まれている
これらを直していく必要がありますね。上記プログラムでいうと67行目の箇所にこれらを直す処理を入れていきます。
数式の再計算と請求金額総額の表示
まずシートの再計算ですが、Worksheetオブジェクトに対するCalculateメソッドです。
これで指定したシートの再計算を行います。
ですから今回の場合は
wsInvoice.Calculate
です、次にセルA18セルに請求金額を表示します。
これは、なんてことはないですね。
wsInvoice.Range("A18").Value = "ご請求金額：" & Format(wsInvoice.Range("D54").Value, "#,##0") & " 円"
Formatを使って、カンマ入りにしています。以下の記事で詳しく説明しています。
請求日と支払期限を算出
こちらは事前にdayCutoffというDate型の変数を宣言しておいて
dayCutoff = wsData.Cells(startRow, 1).Value '納品日
wsInvoice.Range("D15").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 1, 0) '請求日
wsInvoice.Range("D16").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 2, 0) 'お支払期限
とすればOKですね。DateSerialの第3引数をゼロにすると前月末になるというのがミソですね。
余計な行を隠す
行を隠す場合はRowsオブジェクトに対するHiddenプロパティをTrueにします。
今回の場合、品目の行数は(i-startRow)ですから
wsInvoice.Rows(21 + i - startRow & ":50").Hidden = True
とすればOKです。
実行結果
上記の各命令を66行目の下に挿入して実行します。
全てバッチリ、きちんと反映されていますね。
まとめ
これまで8回に渡って、「脱エクセルVBA初心者」を目指して請求書作成プログラムを作成してきました。
- PDFで出力する
- データのコピペを範囲単位で実施
- エラーの回避
など、より実用的かつプログラムの高めるような内容をいくつか習得頂けたのであれば嬉しく思います。
また実用的なVBAプログラムの作り方をお伝えしていきたいと思いますので、どうぞ楽しみにしていてください。
連載目次：【脱エクセルVBA初心者】請求書を自動で作る際のテクニック集
「請求書を自動で作る」というニーズはとても多いですから、その際に便利な機能やテクニックをシリーズにてお伝えしていきます。脱エクセルVBA初心者をターゲットにしています。- 【脱エクセルVBA初心者】新規のワークブックを作成、シートのコピーと名前の変更
- 【脱エクセルVBA初心者】確認メッセージを表示させずにワークシートを削除する方法
- 【脱エクセルVBA初心者】PDFでの出力とファイル名の指定保存そして閉じる
- 【脱エクセルVBA初心者】データが含まれる範囲全体を一発で取得してSortで並び替え
- 【脱エクセルVBA初心者】コピペはRangeが効率的なのでその範囲を見つけるプログラム
- 【脱エクセルVBA初心者】Copyメソッドで範囲まるごとを一発でコピペする
- 【脱エクセルVBA初心者】Vlookupで出てしまうエラーをいい感じに回避する方法
- 【脱エクセルVBA初心者】請求書作成マクロの全体おさらいと総仕上げ
コメント
一生懸命みてやってみていますが、どうしてもできません、、、ここまでの結果のサンプルデータは公開されていないですよね、、、？
nasuさん
コメントありがとうございます！
当ブログではサンプルデータの配布はさせて頂いておりません。
配布したツールをそのまま利用いただくのではなくて、VBAを学んでいただきたいという想いがありまして…申し訳ないです。