Excel VBA ファイルをまとめる

質問

Excel2003にてフォルダにある複数ファイル（8個）の各1番左のシートを
指定したファイルにまとめるVBAを組みたいと思います。

まとめる際、ファイル名の一部を取得し、シート名にしたいのですが可能でしょうか？

例：
ファイル名：20110927_△△△△株式会社.xls　⇒　シート名：△△△△株式会社

※ファイルの個数は固定ですが、年月の部分は変わります。

簡単なVBAの経験しかなく、キーワード検索でもしっくりこないものばかりで
どのようにコードを記述してよいのか非常に困っています。

回答No.1

DOUGLAS_ · ベストアンサー

＞簡単なVBAの経験しかなく、
＞どのようにコードを記述してよいのか非常に困っています。
　このようなトコロでご質問になった場合は、回答者の提示した コード は、そのまま現物の ブック なりでお試しになるのではなくて、予め ダミー の フォルダ・ブック や シート を作成しておいて、よくお試しの上、ご自身で コード の内容を理解なさってから、現物の ブック なりでお使いになってください。

　さて、
＞キーワード検索でもしっくりこないものばかりで
とのことですが、とりあえず

１）「vba フォルダ内の全ファイル」でググります。
２）検索結果の一番上、
●VBA応用(フォルダ内のファイル一覧の取得)
でも、クリック してみましょうか。
３）その ページ の「まずは、Dir関数を用いた単一フォルダ内のファイル一覧の取得です。」に書いてある コード を コピー（[Ctrl] + [C]）します。
４）VBE（Visual Basic Editor）の コード ウィンドウ に ペースト（[Ctrl] + [V]）します。
５）[F5] キー を押下して、「Display_Directory」を実行します。
６）「参照するフォルダ名を入力して下さい。」という ダイアログ が出てきますので、適当な フォルダ の フルパス を入力して [OK] してみてください。
７）以上で、「適当な フォルダ」内のすべての ファイル の名前が、アクティブ な ワークシート に羅列されました。

＞フォルダにある複数ファイル（8個）
というのが、その「フォルダ」の中での、すべての エクセル ブック である場合は、上記の方法で、
Const cnsDIR = "\*.*"
のところを
Const cnsDIR = "\*.xls"
みたいに書き換えれば OK だと存じます。

---------------------------------

＞フォルダにある複数ファイル（8個）
以外にも、その「フォルダ」に エクセル ブック がある場合、「複数ファイル（8個）」の ファイル名 に共通性があるときは、上記と同様に、
Const cnsDIR = "\*hoge*.xls"
などのように書き換えればよろしいが、そうでない場合は、予め、その「複数ファイル（8個）」の ファイル名 を所定の ワークシート 上に羅列しておくような策を講じる必要があります。

==================

　次に、
＞各1番左のシート
につきましては、
１）ＶＢＥ に「worksheet」と入力して [F1] キー を押下します。
２）すると、[ヘルプ ファイル] ダイアログ が開いて、そこに、２つの トピック が表示されていると存じますので、 [Worksheet (オブジ...] の方を開いてみてください。
３）開いた [Worksheet オブジェクト] の ヘルプに、
＞ワークシートのインデックス番号は、ブックのシート見出しの並び順に対応します。
＞Worksheets(1) はブックの最初、つまりシート見出しが最も左側にあるワークシート
と書かれておりますように、
４）「各1番左のシート」は
Worksheets(1)
でイケます。

　なお、ワークシート を コピー したりする場合は、当該の ブック は開いておかなければなりません。

==================

＞ファイル名の一部を取得し、シート名にしたい
につきましては、[Name プロパティ]、[Split 関数] を使うのが簡単かと存じます。
ActiveWorkbook.Name → 20110927_△△△△株式会社.xls
Split(ActiveWorkbook.Name, "_")(0) → 20110927
Split(ActiveWorkbook.Name, "_")(1) → △△△△株式会社.xls
Split(Split(ActiveWorkbook.Name, "_")(1), ".")(0) → △△△△株式会社
Split(Split(ActiveWorkbook.Name, "_")(1), ".")(1) → xls

　従って、「シート名」は
Split(Split(ActiveWorkbook.Name, "_")(1), ".")(0)
になります。

　コピー するために、開いた ブック は、シート の コピー が済んだら、保存せずに閉じてしまった方がよいかと存じますので、コピー する前に、「1番左のシート」の名前を書き換えておきます。
Worksheets(1).Name = Split(Split(ActiveWorkbook.Name, "_")(1), ".")(0)

==================

　さて、ブック を開くのは、
Workbooks.Open Filename:="D:\HOGE\20110927_△△△△株式会社.xls"
のようなことでイケるのはご存じでしょうが、閉じる場合には、
ActiveWorkbook.Close
だけでは、問題があります。

　シート名を書き換えておりますし、ブック によっては、何もしていなくても、「変更を保存しますか？」と聞かれることもありますので、
１）「Close」に カーソル を移動して、[F1] キー を押下し、[Close メソッド] の ヘルプ を開きます。
２）そこに
＞次の使用例は、Book1.xls のブックを閉じます。内容の変更は保存しません。
と書かれているように、
ActiveWorkbook.Close SaveChanges:=False
としなければなりません。

==================

　最後に、肝心の
＞指定したファイルにまとめる
ですが、これは、「指定したファイル」で [新しいマクロの記録] により、次の動作を記録してみてください。
　なお、予め [ウィンドウ(W)] - [整列(A)...] - [並べて表示(T)] しておいてください。
１）「20110927_△△△△株式会社.xls」を開きます。
２）「20110927_△△△△株式会社.xls」の「1番左のシート」の名前を「△△△△株式会社」に変更します。
３）「△△△△株式会社」シート の シート タブ を「指定したファイル」の 最後の シート タブ の後ろの方へ、ドラッグ します。
４）すると、「△△△△株式会社」シート が「20110927_△△△△株式会社.xls」から「指定したファイル」の最後の シートとして移動しました。
５）「20110927_△△△△株式会社.xls」を アクティブ にします。
６）保存せずに終了します。

　以上で、
Sub Macro1()
Workbooks.Open Filename:="D:\HOGE\20110927_△△△△株式会社.xls"
Sheets("Sheet1").Name = "△△△△株式会社"
Sheets("△△△△株式会社").Select
Sheets("△△△△株式会社").Move After:=Workbooks("指定したファイル").Sheets(1)
Windows("20110927_△△△△株式会社.xls").Activate
ActiveWorkbook.Close
End Sub
という コード が得られるかと存じますので、今まで書きましたことを加味して、コード を書き換え、くれぐれも、ダミー の フォルダ・ブック・シート でお試しの上、現物でお試しください。

お礼

質問内容がかなり大ざっぱでしたので、厳しいお言葉を覚悟しておりましたが
とても親切に指導いただきましてありがとうございます。

ダミーのデータでひとつひとつの意味を確認しつつ、ゆっくり試してみました。

シート名についてはSplit 関数を2回使用すればできるんですね！
そのままファイル名を付ける方法はネットに記述がありましたが、
それをどう応用したら良いのか思いつかなかったので本当に助かりました。

回答No.2

imogasi

何も難しい事項は無い。ＷＥＢ照会も経験不足だけ。
（１）在るフォルダのファイルをとらえるのはＷＥＢで「ＶＢＡ　フォルダ　ファイル」で照会すれば沢山記事がある。
＞キーワード検索でもしっくりこないものばかりでーー＞ウソゥォーー
Ｇｏｏｇｌｅででも「ＶＢＡ　ファイル　フォルダ　取得」で照会する
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_110_080.html
・ＦＳＯのＦｏｒ　ＥＡＣＨ法
・Ｄｉｒ関数の繰り返し法
の２つが在る。
ーー
ファイル名を取得して、拡張子がＸＬＳや、ｘｌｓｘ、ｘｌｓｍでなければ読み飛ばす。
参考　本筋のことではないが　http://aerith.mydns.jp/regrets/2007/09/office2007.html
ファイル名をとらえたらオー分する
＞1番左のシートを
　　はシートのＩｎｄｅｘが１のシートをとらえることでよかろう
Sub test01()
MsgBox Worksheets(1).Name
End Sub
で納得して。
＞ファイル名の一部を取得し、シート名にしたいのですが可能でしょうか？
文字列の一部の文字列の切り出しだけの問題だろう
条件をはっきり書け。アンダーバーと株式会社の間のように見えるかどうか。
Ｉｎｓｔｒ関数で出現位置（何文字目か）はわかる。
それで始点と抜き出したい文字列の長さが出る。それらを使ってＭＩＤ関数を使う。
ーー
とらえたファイルの１つに応じて、まとめるほうのシートのＩｎｄｅｘ番号を１づつ増やしていけばよかろう。
ーー
既に詳しい回答が出ている。楽なほうを選んで、この回答は読まないかもしてないが、考え方の筋道の参考と、反発・奮発の材料にして。

お礼

回答いただきましてありがとうございます。
どこをどう組み合わせていけば良いかわからずこちらで質問させていただきました。
今後はズバリな回答を探すだけではなく、ネットで得たヒントを応用する力をつけたいと思います。