【初心者向けエクセルVBA】ファイルのコピーを使って取引先別の請求書を作る

★気に入ったらシェアをお願いします!


みなさん、こんにちは!
毎週土曜日はなんとなくVBAの更新日になっているタカハシ(@ntakahashi0505)です。

請求データ一覧から請求書を自動で作成するシリーズのVBA講座もいよいよ9回目となりました。

前回はWorksheetのコピーと名前の変更を使って複数の取引先の請求書をそれぞれのWorksheetに自動で作成する方法を実現した

【初心者向けエクセルVBA】Worksheetのコピーを活用して複数の請求書を作る
請求データ一覧から請求書を自動で作成するシリーズのVBA講座の第8回!前回は取引先1社の場合でしたが、今回は取引先が複数あったとしてもそれぞれの請求書をいっぺんに作成できるようにしていきたいと思います。

のですが、実はそのままだと少し問題があります。

今回はWorksheetのコピーではなくて、ファイルつまりWorkbookのコピーに方針を切り替えることでその問題を解決する方法を解説したいと思います。

では、よろしくお願いします!

前回のおさらい

モジュールレベル変数の宣言です。

ワークシート初期化用のプロシージャがこちらです。

そして本体のプロシージャです。

取引先マスタに記載されているクライアント数の分だけ

  • 請求書ひな形シートをコピー
  • コピーしたシート名を該当の取引先名にする
  • そのシートに請求データの該当の取引先に関するデータのみ転記をする

という処理を行う形です。

だいぶプログラムっぽくなってきましたね。ただ、このプログラムは一点問題があります。

Workbookのコピーに方針を切り替える

上記プログラムですが初回の実行時はいいのですが、二回目に実行すると

VBAシート名重複によるエラー画面

とこのようにエラーが出てしまいます。

二回目の実行ですと、このように各取引先のシートが存在してしまっています。

VBAシート名が既に存在している

既に存在するシート名と重複するシート名をつけようとしてしまっているために、このようなエラーが出てしまうわけです。

この回避の方法はいくつか考えられるのですが、WorksheetではなくてファイルつまりWorkbookのコピーに方針を切り替えることで解決していきます。

Workbookのコピーを活用した請求書作成の方針

方針としては

  • 「請求書ひな形」をシートを別のファイルに分離して「請求書ひな形.xlsx」ファイルとする
  • 「請求書ひな形.xlsx」をコピーして取引先ごとのファイルを新たに作成する
  • 取引先ごとのファイルに該当の取引先のデータを転記する

というように進めていきます。

FileCopyでファイルをコピーする

ファイルつまりWorkbookのコピー

FileCopy コピー元ファイル, コピー先ファイル

で実行することができます。

このそれぞれのファイルですが、拡張子まで含めたフルパスのファイル名で指定する必要があります。

ThisWorkbook.Pathで現在のフルパスを取得

フルパスを取得する必要がありますので、現在のWorkbookのパスを取得してそれを活用することにします。

従って、コピー元ファイルとなる「請求書ひな形.xlsx」はVBAを組んでいるファイルと同じフォルダに保存してくださいね。

VBAを記述しているWorkbookのパス

ThisWorkbook.Path

で取得することができます。

従って、今回のコピー元、コピー先のそれぞれのファイルのフルパスは

コピー元ファイル:ThisWorkbook.Path & “\請求書ひな形.xlsx”

コピー先ファイル:ThisWorkbook.Path & “\” & Format(dayCutoff, “yyyymm”) & “_” & wsClient.Cells(n, 1).Value & “.xlsx”

でそれぞれ取得することができます。

コピー先ファイルは請求月と取引先名を組み合わせたファイル名で作成しています。

このようにすると、次の月に実行するときに請求月が変わることでファイル名も別になりますから、重複ファイルが存在していることによるエラーを防ぐことができます。

Workbooks.Openでファイルを開く

コピーしたファイルは開かないと活用することができません。

ファイルつまりWorkbookを開くときは

Workbooks.Open 開くファイル

と記述します。

以上を踏まえてプログラムを修正しますと

となります。コピー先のフルパスファイル名はstrFileという変数に格納をしています。

また、シート初期化プロシージャですが

この二行は削除してOKです。というのも、請求書ひな形はもともと初期化されている状態で、それを常にコピーして使うので必要なくなりました。

完成したプログラムを実行すると

VBAファイルのコピー

このようにそれぞれの取引先ごとの請求書が新たなファイルとして生成されます。

まとめ

  • FileCopyでファイルをコピーする
  • ThisWorkbook.Pathで現在のフルパスを取得
  • Workbook.Openでファイルを開く

を使って、取引先ごとに別々のファイルで請求書を作成することができるようになりました。

いずれもかなり使える命令ですので、ぜひ覚えておいてくださいね。

まとめのプログラムはこちらです。

次回ですが、本記事にコメントで頂いているお題について、SumIfs関数とDateAdd関数で解決をしていきますね。

エクセルVBAでDateAdd関数を使って年月を条件としてSumIfs関数を使う方法
エクセルVBAで請求データ一覧から請求書を自動で作成するシリーズです。Excel関数でおなじみのSumIfs関数を使って年月の判定をします。DateAdd関数という日付に追加する便利な関数も紹介しますよ。

どうぞお楽しみにっ!

連載目次:データ一覧から請求書を自動で作る

お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。
  1. 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
  2. 【初心者向けエクセルVBA】For~Next文で簡潔にプログラムを書く
  3. 【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
  4. 【初心者向けエクセルVBA】文字列の連結&Format関数での書式変更
  5. 【初心者向けエクセルVBA】If~Thenを使った条件分岐の超入門
  6. 【初心者向けエクセルVBA】Public変数の宣言とSubプロシージャの呼び出し
  7. 【初心者向けエクセルVBA】InputBoxでの日付入力と月末日の自動算出
  8. 【初心者向けエクセルVBA】Worksheetのコピーを活用して複数の請求書を作る
  9. 【初心者向けエクセルVBA】ファイルのコピーを使って取引先別の請求書を作る
  10. エクセルVBAでDateAdd関数を使って年月を条件としてSumIfs関数を使う方法
  11. 宣言もセットも不要!エクセルVBAでワークシートをオブジェクト名で取り扱う方法
  12. エクセルVBAでシートの列の挿入も簡単に対応できちゃう列挙体の使い方

コメント

  1. ne9999 より:

    勉強さしてもらっています!ありがとうございます!

    質問なのですが、

    FileCopy ActiveWorkbook.Path & “\請求書ひな形.xlsx”, strFile

    の部分でエラー(実行時エラー’70’: 書き込みできません。)
    が出てしまいコピーがうまくできません。

    開いているブックはコピーできないようなのですが、回避するには
    どうしたらよろしいでしょうか。

    • タカハシノリアキ より:

      ご覧いただきましてありがとうございます!

      おっしゃる通り、開いているブックは残念ながらFileCopyでコピーをすることができません。

      記事内では

      ActiveWorkbookファイル名 → 請求データ.xlsm
      コピー元ファイル名 → 請求書ひな形.xlsx

      というファイル名の指定になっています。

      コピー元ファイルである「請求書ひな形.xlsx」を閉じた状態で実行していただければFileCopyができると思います。

      • ne9999 より:

        お返事ありがとうございます。

        なるほど、おっしゃる通りよく考えれば 請求データ.xlsmだけで完結させる必要はなかったんですね。
        ありがとうございます。

        また次のシリーズも楽しみにしています!

  2. べす より:

    ne9999さんと同じところで躓きました。
    一度下まで目を通していたので、読み直して修正、うまくいきました!
    ナイス質問ありがとうございます。

    ともあれ、何度も行きつ戻りつしながらなんとか完成しました。
    これまでもVBAに挑戦しながらもなかなかものにならなかったのですが、
    こちらはとても実用的なサンプルだったので最後までやり通せました。
    メンバーへの支払い明細票を作成したかったので、ほぼそのまま活用出来ます。
    これからひな形の修正に入ります。
    本当にありがとうございました。

    他の記事もこれから読ませていただきますね。

    • べすさん、コメントありがとうございます!

      お役に立てたようで大変うれしく思います。
      なるべく皆さんがスムーズにできるように気を付けておりますが、もし何かお気づきの点がありましたら遠慮なくご連絡を頂ければと思います。

      今後とも「いつも隣にITのお仕事」をどうぞよろしくお願いいたします!

  3. 山本 より:

    「請求データ一覧から請求書を自動で作成する」が何とか完成できました。
    有難うございました、事務仕事に活用したいと思います。
    ただ一つ出来ないことがあります、それは伝票番号の転記です。
    色々と挑戦してみたのですが、素人の私にはどのようにしても無理でした。
    是非ご指導のほど宜しくお願い致します。

    • 山本様、コメントありがとうございます!

      皆さまのお仕事に合わせて具体的な部分のアドバイスを差し上げることも可能ですので、よろしければこちらの問い合わせフォームかチャットワークにて詳細のご連絡を頂ければと思います。

      http://plannauts.co.jp/contact/

      どうぞよろしくお願いいたします。

  4. サトウ より:

    こんにちは!とても分かりやすく、初心者の私でも何とかゴール目前までたどりつくことができました。ありがとうございます。

    …ただ、最終回のプログラムのうち、
    FileCopy ActiveWorkbook.Path & “\請求書ひな形.xlsx”, strFile
    の部分について、エラー(実行時エラー “53” ファイルが見つかりません)となります。

    ひな形と請求データのファイルは同じフォルダに入れましたし、
    書き込んだプログラム自体をチェックしても素人目には問題なさそうで…。

    何か思い当たる事象ないでしょうか・・・?

    • サトウ様

      いつもご覧いただきましてありがとうございます。

      エラー内容からすると明らかに「請求書ひな形.xlsx」が存在していないのが理由とは思われるのですが、それが原因でないとすると、なかなか、わかりかねますね…

  5. ヤマザキ より:

    タカハシ様

    エクセル克服できるようこちらのサイトで勉強させてもらっています!
    初心者でもわかりやすく助かっています。

    改めまして同じエラーメッセージがでたので書き込みさせていただきました。

    ひな形エクセルが原因かと思ったところ、実は前の行の記述ミスが原因でした。
    (コピー先ファイル名の記述にエクセルの拡張子入れ忘れ)

    修正したら無事成功しました!
    もし同様の原因でしたら参考になれば幸いです。

  6. Kana.M より:

    VBA初心者です。
    とてもわかりやすく、ありがたく勉強させて頂いています。
    自力でなんとかと思ったのですが難しく、ご教示頂ければ幸いです。

    最終回の「ファイルコピーで得意先別請求書を作る」ですが、当月納品のない得意先もあると仮定して納品のあった得意先のみ請求書ファイルを作成する処理を教えて下さい。

    条件文はすでに記載のある転記処理の上のif文でよいと思うのですが、ファイルコピーをこの中に入れるとまずいでしょうし、if文の位置をトップに持ってくると考えると、全体の構成変更が必要のような気がしますし、頭の整理できなくなってしまいました。
    構成変更のヒントでも結構です。
    宜しくお願い致します。

    • Kana.Mさん

      コメントありがとうございます。

      For n = 2 To rowsClient ~ Next n

      の中のブロック全体について、該当の取引先の該当月の納品がなければ処理をしない、とすればよいですね。

      せっかくご質問頂いたので、記事にしてみたいと思います。
      少しお待ちください。

  7. 配車係のSoriani より:

    こんにちは!こちらを参考にして仕事場での請求書を作成させて頂いてます。とても分かりやすく参考になります。質問なんですが、転記する期間を「yyyy/mm/dd」~「yyyy/mm/dd」までと入力方式での抽出にする事は可能でしょうか?あと、転記する取引先を入力もしくは選択方式で抽出できれば当方職場で完全に運用できる仕上がりになるのですが。是非ご教授頂ければ幸いです、宜しくお願い致します。

    • 配車係のSriani様

      ご質問ありがとうございます!
      なるほど…期間と取引先を詳細に入力する…ということですね。
      需要ありそうですね。

      機会を見て記事にできればと思いますので、気長にお待ちいただけると助かります。
      今後ともどうぞよろしくお願いいたします。