By: *** Fanch The System !!! ***
みなさん、こんにちは！
毎週土曜日はなんとなくVBAの更新日になっているタカハシ(@ntakahashi0505)です。
請求データ一覧から請求書を自動で作成するシリーズのVBA講座もいよいよ9回目となりました。
前回はWorksheetのコピーと名前の変更を使って複数の取引先の請求書をそれぞれのWorksheetに自動で作成する方法を実現した
のですが、実はそのままだと少し問題があります。
今回はWorksheetのコピーではなくて、ファイルつまりWorkbookのコピーに方針を切り替えることでその問題を解決する方法を解説したいと思います。
では、よろしくお願いします！
前回のおさらい
モジュールレベル変数の宣言です。
Option Explicit
Dim wsData As Worksheet '「請求データ」シートを入れるオブジェクト変数
Dim wsInvoice As Worksheet '「請求書ひな形」シートを入れるオブジェクト変数
Dim wsClient As Worksheet '「取引先マスタ」シートを入れるオブジェクト変数
Dim rowsData As Long '「請求データ」の行数
Dim rowsClient As Long '「取引先マスタ」の行数
ワークシート初期化用のプロシージャがこちらです。
Sub シート初期化()
Set wsData = ThisWorkbook.Worksheets("請求データ")
Set wsInvoice = ThisWorkbook.Worksheets("請求書ひな形")
Set wsClient = ThisWorkbook.Worksheets("取引先マスタ")
rowsData = wsData.Cells(Rows.Count, 2).End(xlUp).Row '「請求データ」の最後の行数を取得
rowsClient = wsClient.Cells(Rows.Count, 1).End(xlUp).Row '「取引先マスタ」の最後の行数を取得
wsInvoice.Rows("21:50").Hidden = False '隠れている行を再表示する
wsInvoice.Range("A21:C50").Clear
End Sub
そして本体のプロシージャです。
Sub 請求書作成()
Dim i As Long, j As Long, k As Long, n As Long 'For～Nextカウント用整数型変数
Dim dayData As Date '納品日格納用変数
Dim dayCutoff As Date '締月入力用変数
Dim strClient As String '取引先格納用変数
dayCutoff = InputBox("締月を入力してください（例：2015/5）")
For n = 2 To rowsClient
Call シート初期化
wsInvoice.Copy after:=wsInvoice '請求書ひな形シートを右隣りにコピーする
ActiveSheet.Name = wsClient.Cells(n, 1).Value 'シート名を取引先名に変更する
Set wsInvoice = ActiveSheet '※シートコピーをすると新たにできたシートがアクティブ
k = 21 '請求書ひな形シート用カウント変数、スタートは21行目
For i = 2 To rowsData 'iは請求データ用のカウント変数、最終値の設定にrowsDataを使う
strClient = wsData.Cells(i, 2).Value '現在の行のクライアント名を取得
dayData = wsData.Cells(i, 1).Value '現在の行の納品日を取得
If strClient = wsClient.Cells(n, 1).Value Then
If Year(dayData) = Year(dayCutoff) And Month(dayData) = Month(dayCutoff) Then '年が2015でかつ月が5の場合は処理を実行
For j = 1 To 3
'請求データの2+i行目を請求書ひな形の21+i行目に転記
wsInvoice.Cells(k, j).Value = wsData.Cells(i, j + 2).Value
Next j
k = k + 1
End If
End If
Next i
wsInvoice.Rows(k & ":50").Hidden = True 'データがない行を隠す
wsInvoice.Calculate '「請求書ひな形」シートを再計算する
wsInvoice.Range("A18").Value = "ご請求金額：" & Format(wsInvoice.Range("D54").Value, "#,##0") & " 円"
wsInvoice.Range("D15").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 1, 0) '請求日
wsInvoice.Range("D16").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 2, 0) 'お支払期限
Next n
End Sub
取引先マスタに記載されているクライアント数の分だけ
- 請求書ひな形シートをコピー
- コピーしたシート名を該当の取引先名にする
- そのシートに請求データの該当の取引先に関するデータのみ転記をする
という処理を行う形です。
だいぶプログラムっぽくなってきましたね。ただ、このプログラムは一点問題があります。
Workbookのコピーに方針を切り替える
上記プログラムですが初回の実行時はいいのですが、二回目に実行すると
とこのようにエラーが出てしまいます。
二回目の実行ですと、このように各取引先のシートが存在してしまっています。
既に存在するシート名と重複するシート名をつけようとしてしまっているために、このようなエラーが出てしまうわけです。
この回避の方法はいくつか考えられるのですが、WorksheetではなくてファイルつまりWorkbookのコピーに方針を切り替えることで解決していきます。
Workbookのコピーを活用した請求書作成の方針
方針としては
- 「請求書ひな形」をシートを別のファイルに分離して「請求書ひな形.xlsx」ファイルとする
- 「請求書ひな形.xlsx」をコピーして取引先ごとのファイルを新たに作成する
- 取引先ごとのファイルに該当の取引先のデータを転記する
というように進めていきます。
FileCopyでファイルをコピーする
ファイルつまりWorkbookのコピーは
で実行することができます。
このそれぞれのファイルですが、拡張子まで含めたフルパスのファイル名で指定する必要があります。
ThisWorkbook.Pathで現在のフルパスを取得
フルパスを取得する必要がありますので、現在のWorkbookのパスを取得してそれを活用することにします。
従って、コピー元ファイルとなる「請求書ひな形.xlsx」はVBAを組んでいるファイルと同じフォルダに保存してくださいね。
VBAを記述しているWorkbookのパスは
で取得することができます。
従って、今回のコピー元、コピー先のそれぞれのファイルのフルパスは
コピー先ファイル：ThisWorkbook.Path & “\” & Format(dayCutoff, “yyyymm”) & “_” & wsClient.Cells(n, 1).Value & “.xlsx”
でそれぞれ取得することができます。
コピー先ファイルは請求月と取引先名を組み合わせたファイル名で作成しています。
このようにすると、次の月に実行するときに請求月が変わることでファイル名も別になりますから、重複ファイルが存在していることによるエラーを防ぐことができます。
Workbooks.Openでファイルを開く
コピーしたファイルは開かないと活用することができません。
ファイルつまりWorkbookを開くときは
と記述します。
以上を踏まえてプログラムを修正しますと
～
Dim strFile As String 'コピー先パス＆ファイル名
Call シート初期化
dayCutoff = InputBox("締月を入力してください（例：2015/5）")
For n = 2 To rowsClient
strFile = ThisWorkbook.Path & "\" & Format(dayCutoff, "yyyymm") & "_" & wsClient.Cells(n, 1).Value & ".xlsx" 'コピー先ファイル名
FileCopy ThisWorkbook.Path & "\請求書ひな形.xlsx", strFile 'ファイルをコピー
Workbooks.Open strFile 'コピーにて作成したファイルを開く
Set wsInvoice = ActiveSheet '開いたファイルはアクティブなので、開いたワークシートをオブジェクト変数にセット
k = 21 '請求書ひな形シート用カウント変数、スタートは21行目
～
となります。コピー先のフルパスファイル名はstrFileという変数に格納をしています。
また、シート初期化プロシージャですが
wsInvoice.Rows("21:50").Hidden = False '隠れている行を再表示する
wsInvoice.Range("A21:C50").Clear
この二行は削除してOKです。というのも、請求書ひな形はもともと初期化されている状態で、それを常にコピーして使うので必要なくなりました。
完成したプログラムを実行すると
このようにそれぞれの取引先ごとの請求書が新たなファイルとして生成されます。
まとめ
- FileCopyでファイルをコピーする
- ThisWorkbook.Pathで現在のフルパスを取得
- Workbook.Openでファイルを開く
を使って、取引先ごとに別々のファイルで請求書を作成することができるようになりました。
いずれもかなり使える命令ですので、ぜひ覚えておいてくださいね。
まとめのプログラムはこちらです。
Option Explicit
Dim wsData As Worksheet '「請求データ」シートを入れるオブジェクト変数
Dim wsInvoice As Worksheet '「請求書ひな形」シートを入れるオブジェクト変数
Dim wsClient As Worksheet '「取引先マスタ」シートを入れるオブジェクト変数
Dim rowsData As Long '「請求データ」の行数
Dim rowsClient As Long '「取引先マスタ」の行数
Sub シート初期化()
Set wsData = ThisWorkbook.Worksheets("請求データ")
Set wsClient = ThisWorkbook.Worksheets("取引先マスタ")
rowsData = wsData.Cells(Rows.Count, 2).End(xlUp).Row '「請求データ」の最後の行数を取得
rowsClient = wsClient.Cells(Rows.Count, 1).End(xlUp).Row '「取引先マスタ」の最後の行数を取得
End Sub
Sub 請求書作成()
Dim i As Long, j As Long, k As Long, n As Long 'For～Nextカウント用整数型変数
Dim dayData As Date '納品日格納用変数
Dim dayCutoff As Date '締月入力用変数
Dim strClient As String '取引先格納用変数
Dim strFile As String 'コピー先パス＆ファイル名
Call シート初期化
dayCutoff = InputBox("締月を入力してください（例：2015/5）")
For n = 2 To rowsClient
strFile = ThisWorkbook.Path & "\" & Format(dayCutoff, "yyyymm") & "_" & wsClient.Cells(n, 1).Value & ".xlsx" 'コピー先ファイル名
FileCopy ThisWorkbook.Path & "\請求書ひな形.xlsx", strFile 'ファイルをコピー
Workbooks.Open strFile 'コピーにて作成したファイルを開く
Set wsInvoice = ActiveSheet '開いたファイルのワークシートをセット
k = 21 '請求書ひな形シート用カウント変数、スタートは21行目
For i = 2 To rowsData 'iは請求データ用のカウント変数、最終値の設定にrowsDataを使う
strClient = wsData.Cells(i, 2).Value '現在の行のクライアント名を取得
dayData = wsData.Cells(i, 1).Value '現在の行の納品日を取得
If strClient = wsClient.Cells(n, 1).Value Then
If Year(dayData) = Year(dayCutoff) And Month(dayData) = Month(dayCutoff) Then '年が2015でかつ月が5の場合は処理を実行
For j = 1 To 3
'請求データの2+i行目を請求書ひな形の21+i行目に転記
wsInvoice.Cells(k, j).Value = wsData.Cells(i, j + 2).Value
Next j
k = k + 1
End If
End If
Next i
wsInvoice.Rows(k & ":50").Hidden = True 'データがない行を隠す
wsInvoice.Calculate '「請求書ひな形」シートを再計算する
wsInvoice.Range("A18").Value = "ご請求金額：" & Format(wsInvoice.Range("D54").Value, "#,##0") & " 円"
wsInvoice.Range("D15").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 1, 0) '請求日
wsInvoice.Range("D16").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 2, 0) 'お支払期限
Next n
End Sub
次回ですが、本記事にコメントで頂いているお題について、SumIfs関数とDateAdd関数で解決をしていきますね。
どうぞお楽しみにっ！
連載目次：データ一覧から請求書を自動で作る
お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。- 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
- 【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法
- 【初心者向けエクセルVBA】For～Next文で簡潔にプログラムを書く
- 【初心者向けエクセルVBA】行の数をカウントする＆不要な行を隠す
- 【初心者向けエクセルVBA】文字列の連結＆Format関数での書式変更
- 【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法
- 【初心者向けエクセルVBA】ワークシートのデータのある範囲だけをピッタリ取得する方法
- 【初心者向けエクセルVBA】セル範囲の平行移動をする方法・リサイズをする方法
- 【初心者向けエクセルVBA】日付データから年・月・日を取り出す
- 【初心者向けエクセルVBA】If～Thenを使った条件分岐の超入門
- 【初心者向けエクセルVBA】For～Next文でセル範囲を一行ずつ移動させる
- 【初心者向けエクセルVBA】セル範囲のクリア～ClearContentsメソッドとClearメソッド
- 【初心者向けエクセルVBA】ワークシート・セルを選択する方法の色々について
- 【初心者向けエクセルVBA】入力ダイアログを表示するInputBoxメソッドの使い方
- 【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する
- 【初心者向けエクセルVBA】ワークシートをコピーする方法とそのシート名を変更する方法
- 【初心者向けエクセルVBA】オブジェクトを変数にセットして取り扱う方法
- 【初心者向けエクセルVBA】Openメソッドで新たなブックを開く方法
- 【初心者向けエクセルVBA】現在マクロを書いているブックのフォルダパスを取得する
- 【初心者向けエクセルVBA】開いたブックとそのワークシートをオブジェクト変数にセットする
- 【初心者向けエクセルVBA】ワークブックを別名で保存して閉じる方法
- 【初心者向けエクセルVBA】取引先別に請求書を作成するマクロを作る
コメント
勉強さしてもらっています！ありがとうございます！
質問なのですが、
FileCopy ActiveWorkbook.Path & “\請求書ひな形.xlsx”, strFile
の部分でエラー（実行時エラー’70’: 書き込みできません。）
が出てしまいコピーがうまくできません。
開いているブックはコピーできないようなのですが、回避するには
どうしたらよろしいでしょうか。
ご覧いただきましてありがとうございます！
おっしゃる通り、開いているブックは残念ながらFileCopyでコピーをすることができません。
記事内では
ActiveWorkbookファイル名 → 請求データ.xlsm
コピー元ファイル名 → 請求書ひな形.xlsx
というファイル名の指定になっています。
コピー元ファイルである「請求書ひな形.xlsx」を閉じた状態で実行していただければFileCopyができると思います。
お返事ありがとうございます。
なるほど、おっしゃる通りよく考えれば 請求データ.xlsmだけで完結させる必要はなかったんですね。
ありがとうございます。
また次のシリーズも楽しみにしています！
ne9999さんと同じところで躓きました。
一度下まで目を通していたので、読み直して修正、うまくいきました！
ナイス質問ありがとうございます。
ともあれ、何度も行きつ戻りつしながらなんとか完成しました。
これまでもVBAに挑戦しながらもなかなかものにならなかったのですが、
こちらはとても実用的なサンプルだったので最後までやり通せました。
メンバーへの支払い明細票を作成したかったので、ほぼそのまま活用出来ます。
これからひな形の修正に入ります。
本当にありがとうございました。
他の記事もこれから読ませていただきますね。
べすさん、コメントありがとうございます！
お役に立てたようで大変うれしく思います。
なるべく皆さんがスムーズにできるように気を付けておりますが、もし何かお気づきの点がありましたら遠慮なくご連絡を頂ければと思います。
今後とも「いつも隣にITのお仕事」をどうぞよろしくお願いいたします！
「請求データ一覧から請求書を自動で作成する」が何とか完成できました。
有難うございました、事務仕事に活用したいと思います。
ただ一つ出来ないことがあります、それは伝票番号の転記です。
色々と挑戦してみたのですが、素人の私にはどのようにしても無理でした。
是非ご指導のほど宜しくお願い致します。
山本様、コメントありがとうございます！
皆さまのお仕事に合わせて具体的な部分のアドバイスを差し上げることも可能ですので、よろしければこちらの問い合わせフォームかチャットワークにて詳細のご連絡を頂ければと思います。
http://plannauts.co.jp/contact/
どうぞよろしくお願いいたします。
こんにちは！とても分かりやすく、初心者の私でも何とかゴール目前までたどりつくことができました。ありがとうございます。
…ただ、最終回のプログラムのうち、
FileCopy ActiveWorkbook.Path & “\請求書ひな形.xlsx”, strFile
の部分について、エラー(実行時エラー “53” ファイルが見つかりません)となります。
ひな形と請求データのファイルは同じフォルダに入れましたし、
書き込んだプログラム自体をチェックしても素人目には問題なさそうで…。
何か思い当たる事象ないでしょうか・・・？
サトウ様
いつもご覧いただきましてありがとうございます。
エラー内容からすると明らかに「請求書ひな形.xlsx」が存在していないのが理由とは思われるのですが、それが原因でないとすると、なかなか、わかりかねますね…
タカハシ様
エクセル克服できるようこちらのサイトで勉強させてもらっています！
初心者でもわかりやすく助かっています。
改めまして同じエラーメッセージがでたので書き込みさせていただきました。
ひな形エクセルが原因かと思ったところ、実は前の行の記述ミスが原因でした。
（コピー先ファイル名の記述にエクセルの拡張子入れ忘れ）
修正したら無事成功しました！
もし同様の原因でしたら参考になれば幸いです。
ヤマザキ様
コメントありがとうございます！
貴重な情報助かります。
今後ともどうぞよろしくお願いいたします！
VBA初心者です。
とてもわかりやすく、ありがたく勉強させて頂いています。
自力でなんとかと思ったのですが難しく、ご教示頂ければ幸いです。
最終回の「ファイルコピーで得意先別請求書を作る」ですが、当月納品のない得意先もあると仮定して納品のあった得意先のみ請求書ファイルを作成する処理を教えて下さい。
条件文はすでに記載のある転記処理の上のif文でよいと思うのですが、ファイルコピーをこの中に入れるとまずいでしょうし、if文の位置をトップに持ってくると考えると、全体の構成変更が必要のような気がしますし、頭の整理できなくなってしまいました。
構成変更のヒントでも結構です。
宜しくお願い致します。
Kana.Mさん
コメントありがとうございます。
For n = 2 To rowsClient ～ Next n
の中のブロック全体について、該当の取引先の該当月の納品がなければ処理をしない、とすればよいですね。
せっかくご質問頂いたので、記事にしてみたいと思います。
少しお待ちください。
こんにちは！こちらを参考にして仕事場での請求書を作成させて頂いてます。とても分かりやすく参考になります。質問なんですが、転記する期間を「yyyy/mm/dd」～「yyyy/mm/dd」までと入力方式での抽出にする事は可能でしょうか？あと、転記する取引先を入力もしくは選択方式で抽出できれば当方職場で完全に運用できる仕上がりになるのですが。是非ご教授頂ければ幸いです、宜しくお願い致します。
配車係のSriani様
ご質問ありがとうございます！
なるほど…期間と取引先を詳細に入力する…ということですね。
需要ありそうですね。
機会を見て記事にできればと思いますので、気長にお待ちいただけると助かります。
今後ともどうぞよろしくお願いいたします。
いつも楽しく勉強させていただいてます。ありがとうございます。
お手すきのときにご回答いただければ幸いです。
現象として2つ、余計なブックを作ろうとする、二社のブックが作られても必要項目の転機や計算がなされていない、が発生します。今回は2社のはずですがマクロを回すともう一つブックを作ろうとして結果「書き込めない」とエラーが出て終わります。よく見たら取引先マスタにひな形の一部の記述が加えられていました。またできた二社のブックにしても請求書ひな形.xlsxをコピーしただけで必要項目の転記や計算がされてません。
そこで、Set wsInvoice = ActiveSheetの部分のActiveSheetに意図しないシートが指定されているのかなと考えて、いくら調べても検討もどうにも分かりません。また請求書ひな形.xlsxファイルを作る際は、元々のファイルから単純に請求書ひな形シート”のみ”を分離すればよろしいのでしょうか。よろしくご教授いただけます事をお願いいたします。
度々失礼します。自分なりに色々試して進捗がありましたので書かせていただきます。結論から言うとSet wsInvoice = ActiveSheetの、ActiveSheetについて意図したシート（コピーしてできた各社のブックのシート）が指定されておらず、あらぬシートが指定されていることが分かりました。明示的にブックとシートを指定すれば問題なくマクロが回りました。稚拙な内容で申し訳ございませんでした。
くらさま
コメントありがとうございます。
そして、返信が遅くなりすみません。
無事に解決されたようで良かったです。
ActiveSheet…使わないようなサンプルにしたほうがよさそうですね。検討をしたいと思います。
引き続き、当ブログをどうぞよろしくお願いいたします。