選択したフォルダ内の全てのブックを開いて作業するには?

Question 55.2   Previous Next
基本情報ブックの元DATAシートを、任意のフォルダ(サブも含む)内のブックすべての元DATAシートに、リンク貼り付けするには? (任意のフォルダのブックには、必ず元DATAシートが存在します。)
この質問をされた方へ ご連絡したE-mailが戻ってしまいました。正しいアドレスをお知らせください。
Answer   Copyright (C) 2002.9.12 永井善王
短い文章で大掛かりな質問が寄せられました。
「リンク貼り付け」に重点を置くと、どこをどうリンクさせたいのか不明ですし、なぜ、たくさんのブックにそのようなことをしたいのか推察できないので誠にお答えしにくいです。リンク貼り付けのコードは簡単に自動記録できますから、悩むような問題ではありませんし。
おそらくポイントは、任意のフォルダ内の全てのブックを開くためのマクロを作成する方法ではないかと、勝手に解釈して回答します。

この「すぐに役立つエクセルVBAマクロ集」には、この課題に役立ちそうなマクロをいくつか掲載しています。
特に、5)
フォルダ名を取得する と、1) フォルダ中のファイル名をシートに書く のページは、見ていただいておれば良かったのにと思います。ですが、それらのページには、サブフォルダ内のファイル名を取得する方法が載っていませんので、以下の解説の中で取り上げます。

環境を整理しておきましょう。ブックはハードディスクの Cドライブに、下図のように保存されていることにします。
親フォルダ
サブフォルダ
「親データ」フォルダの中の「マスター.xls」が、元になる基本情報ブックです。そして「Book1.xls」と「Book2.xls」、サブフォルダ「子データ」の中にある「Book3.xls」がリンク貼り付け先のブックです。

前もってお断りしておきますが、結構長く複雑なマクロになりますので、ここに全部を掲載することができません。サンプルブックをダウンロードして、そこにあるマクロを見ながら理解してください。
     ダウンロードは
ここをクリック (YNxv929.lzh 29KB 圧縮ファイル)
ダウンロードしたファイルを解凍すると上図のテストデータとサンプルブック(YNxv929_Dir.xls)が現れます。マクロを動作させてみたい場合は、解凍してできた「親データ」フォルダをハードディスクのCドライブの\(ルート)へ移動させてから、サンプルブックの[選択したフォルダ内の全てのブックを開いて作業する]ボタンをクリックします。

このマクロは、Excelシートを利用して処理過程が目に見えるように組んでいます。配列を使ってプロ的に組む方法も考えられますが、目に見えないところで処理するとデバックに余計な労力が掛かる場合がありますから、お勧めできません。
 [ファイルを開く] ダイアログ
【処理の流れと概要】
1. 元になる基本情報ブックへのパスを取得する
GetOpenFilenameメソッドで [ファイルを開く] ダイアログボックス(右図)を表示して、ユーザーに基本情報ブックを選択してもらうことによってパスとファイル名を取得する。(そのファイルは実際には開かれない)

フォルダ名シート 2. フォルダ名とサブフォルダ名を取得してパスをシート(左図)にセットする
基本情報ブックへのパスを利用してDir関数でフォルダ名とサブフォルダ名を取得し、パスを組んでセットする
ファイル名シート

3. 各フォルダ中のファイル名を取得してシート(右図)にセットする
各フォルダごとにDir関数でファイル名を取得する

4. シートにセットされたパス名とファイル名でリンク先ブックを順に開いてリンク貼り付けする
そのとき、シートにセットされたファイル名が基本情報ファイルの名前と同じ(上図のB3セル)なら処理しない。リンク元、リンク先ともに「元データ」シートのA2:B2セルにしている

関連ページ: 月別フォルダのブックを順に開いて一覧表を作成するには
参考図書:  Excel VBAマクロ組み方講座 実践編第1章6 指定フォルダ内の全てのブックを順に開く

 

Excel VBA Macro