---topics:フォルダ内のブックを開く、dir関数---

VBA編其ノ13 複数のブックを合体

前回・前々回で、同じフォーマットのデータがひとつのExcelの複数のシートに散らばってるのを合体するというマクロを作りました。 それに対して今回は、複数のファイル(複数のブックを合体するというのがミッションです。 カテゴリーや担当者ごとにExcelを分けて運用すること、よくあると思いますが、そうすると面倒なのが「合体」。 「シートをまとめる」は前回やったので、今回は「ブックを合体」やってみたいと思いますが、はたして。

のっけから答え、言っちゃうわね

まず、複数のブックがあるって、こんな感じ↓ね※1~※6

ひとつのフォルダ内に、Excelが5コあって
※1 ひとつのフォルダ内に、Excelが5コあって
aaaa.xlsx 項目は1~4まで
※2 aaaa.xlsx 項目は1~4まで
bbbb.xlsx フォーマットは全部同じ
※3 bbbb.xlsx フォーマットは全部同じ
cccc.xlsx コイツの中身は空(ヘッダーだけ)
※4 cccc.xlsx コイツの中身は空(ヘッダーだけ)
dddd.xls コイツだけ拡張子がxls
※5 dddd.xls コイツだけ拡張子がxls
eeee.xlsx
※6 eeee.xlsx

aaaa.xlsx、bbbb.xlsx、cccc.xlsx、dddd.xls、eeee.xlsxは、項目1から項目4までの4列構成で、全部同じフォーマットです。 コイツらをひとつのExcelにまとめたい。 全レコード合体。

で、今回はいきなり回答からです。 下記のマクロ、動かしてみてください。

下記、使い方。

1. このマクロを入れた、それだけ単独のブックをひとつ作ります(下記の例で言うと、「フォルダ内のエクセルを合体20170606.xlsm」がそれ)。

2. んで、合体したいExcel(上記の例で言うとaaaa.xlsx~eeee.xlsx)をひとつのフォルダにぶちこみます※7。 デスクトップにフォルダを作って、そこに入れるとよいでしょう。 サーバ上で動かすのはやめておいた方がよいかな。 合体するならローカルで。

3. そして、マクロを入れたブックも同フォルダに一緒に入れます。

4. そしたら、マクロ入りブックを開き、マクロを動かします※8。 他のExcelは開かなくて大丈夫です。 マクロが自動で各Excelを開いてくれますので(というか、他のExcelを開いているとエラーになる)。

で、最終的に、合体できる、と※9

合体したいExcelをひとつのフォルダに入れ、マクロ入りExcelもいっしょに入れる
※7 合体したいExcelをひとつのフォルダに入れ、マクロ入りExcelもいっしょに入れる
マクロ入りExcelを開き、マクロを動かす
※8 マクロ入りExcelを開き、マクロを動かす
Excelが次々開き、合体する
※9 Excelが次々開き、合体する

下記、このマクロの注意点。
■1行目1列目から(左上ぴっちりから)表が始まっている、というのを前提としています。 例によって例のごとく。
■フォーマットが同じでないと意味がありません。
■複数シートをもっている場合でも、各Excelのシート1しか合体できません。全ブック・全シート合体は後述。
■マクロ動かすときはローカルで。

みなさん、うまくいきましたでしょうか。

解説:変数の宣言から

このマクロ、読んでいただけるとおわかりになるでしょうが、…けっこうむずかしいですね。 はじめて登場するワードもありますし。 なので、ゆっくり見ていくことにしましょう。 まずは変数の宣言から。

【変数の宣言】
kihonClmn:kihon Column。最終行取得の基本となる列。
myPath:my Path。合体作業をするフォルダのパス。
myFname1:my File name 1。ファイル名格納用変数その1。ヘッダー作成用の捨て石。
myFname2:my File name 2。ファイル名格納用変数その2。こっちが本番。aaaa.xlsx~eeee.xlsxを格納する。
newbk:new book。合体用の新規ブックを格納。
i:件数数え用。
lastRw:last Row。aaaa.xlsx~eeee.xlsxの最終行を取得しに行く。
lastRwnewbk:last Row of new book。合体後のExcelの最終行を取得。「最終行+1」にコピペしなきゃいけないので。

変数の名前の付け方については僕の方で勝手につけてるだけなので、そこを一生懸命覚えようとしなくてもよいですよ。
名前自体は別に「aaa」でも「あああ」でもよいんです、マクロ書いてる本人がわかれば。 大事なのは考え方です。 なぜこの変数が必要なのか。 その辺りを読み解きながら、コードを読んでいってください。

さて。
kihonClmnは最終行取得の基本となる列数を入れます。 このマクロは1列目1行目から表がはじまっていることを前提としているので、「1」を入れています※10。 なので、「ウチは1列目はアキで、2列目から表が始まっているよ」という方は、この値を「2」に直してあげてください。

何列目を使って最終行を取得するかを設定
※10 何列目を使って最終行を取得するかを設定

次。
このマクロでは「フォルダ内のExcelを順次検索して、開く」という動作をするために、「パス」を格納する変数を用意しています。 それがmyPath
「パス」って言うのはファイルの住所みたいなものだと思ってください。 フォルダの上部のココ↓をクリックすると出てくる「C:\Users\知巳\Desktop\じっけんよう」ってのがそれね※11。 これは、「「Cドライブ」の中の、「Users」の中の、「知巳」の中の、「Desktop」の中の、「じっけんよう」ってフォルダです」って意味になります。

パスの例
※11 パスの例

myFname1myFname2には検索したファイル名を格納します。

上記パスに関連して。
上記パスを区切っている文字「\」ですが、¥(円マーク)と入力してるのに\(バックスラッシュ)と表示されたり…。
実は¥も\もPC上は同じものとして扱われてます。 ただ表示形式の違いで見え方が変わるだけですので、¥と\については「そういうものなんだな」と思うようにしてください。 深い理由は僕もよく知らないんですけど…。

ハイ、次。
newbkは新規Excelとの切替に使います。 iは総件数を数える用に。 この辺りは前回のシート合体マクロと同様ですね。
lastRwは、aaaa.xlsx~eeee.xlsxの各Excelの最終行を見に行く用に使います。 で、lastRwnewbkの方はコピペ先の合体後Excelの最終行を見に行きます。 各Excelを「最終行+1」にコピペする、というのが合体の仕組みですので※12。 この辺りも前回のシート合体マクロ同様ですね。

最終行を取得し、最終行の次の行に貼り付ける
※12 最終行を取得し、最終行の次の行に貼り付ける

dir関数とは何ぞや

ではいよいよ中身です。
まず、Set newbk = Workbooks.Add()で新しくブックを追加し、それを変数に格納します※13。 この辺は前回やりましたね。 覚えていますでしょうか。

新規ブックを変数にセット
※13 新規ブックを変数にセット

で、ここからがいよいよ新しいヤツ。 dir関数ってヤツを使います。 dir関数は、

dir(検索条件)

ってかたちで使うVBA用の関数で、もし検索条件に合致するファイルが存在すればそのファイル名を返す、というはたらきをします。 存在しなければ""(空の文字列)を返します。 これ実は、後々ループ処理させる地味に大事になってきます。
で、このdir関数のかっこの中、「検索条件」として「パス」と「拡張子」を指定してやれば、それに該当するファイル名が返ってくる。
今回、拡張子はワイルドカードを使って「*.xls*」とします。 こうすれば、.xlsxも、.xlsmも、.xlsも拾ってくれます。
で、「パス」は、作業するフォルダのパス、つまりマクロの入ったブックとイコールなので、まずもってmyPath = ThisWorkbook.Pathでパスを取得、そのうえでmyPath & "\" & "*.xls*"\はパスの区切り文字)としてdir関数の引数に入れてやります※14

dir(パス + \ + ファイル名)
※14 dir(パス + \ + ファイル名)

…ちょっとむずかしいかな?
まあ、とりあえずこのかたちで覚えちゃう、というのもひとつの手です。 使ってみて、後々理解が追いついてくる、ということもありますし。 でもまあ、わからないながらも一度解説文を読んでおく、というのもひとつの経験です。 なので、いちおうは最後までお付き合い願えると幸いです。
さて。
dir関数の仕様というか特徴なんですが、dir関数はファイル名を1コずつ拾います。 なので、

dir関数でファイル名を拾う

拾ったファイル名のExcelをオープン→コピペ

再びdir関数でファイル名を拾う

(以下ループ)

という手順でループさせれば、フォルダ内のExcelを次々開いていってコピペできるのでは。 これが今回のマクロの基本的な考え方です。
だから下記で、ファイルを開きコピペしそのファイルを閉じています※15

ファイルを開く、コピペ、閉じる
※15 ファイルを開く、コピペ、閉じる

ファイルを開くときは.Open()、閉じるには.Closeを使います。 .Closeの後ろのFalseは「保存しないで閉じる」の意味です。 単に閉じるだけだったら.Closeだけでもよいんですが、そうすると閉じる際に「変更を保存しますか?」とExcelに聞かれちゃいます。 別に1回聞かれるぐらいだったらそんなに面倒でもないのですが、…今回のように「次々開いて、閉じる」系のマクロだといちいち閉じるたびに聞かれちゃうので、「保存しないで閉じる」ようにあらかじめ命令してあげます。
もし「保存して、閉じる」をやりたい場合は、

ブック名.Save
ブック名.Close

としてやると、「保存して、閉じる」を実行できますよ。

さて。
このマクロは大きく2つのパートに分かれていて、最初のパートはヘッダー部分(1行目)をもってくるだけ。 本番はループ処理のところになります※16

2つのパートに分かれる
※16 2つのパートに分かれる

で、ループ処理のところ。
最初にmyFname2 = Dir(myPath & "\" & "*.xls*")でファイル名を取得するのですが、その後は引数を省略しmyFname2 = Dir()だけで次のファイル名を取得してくれます※17。 だから次々ファイルを開ける、というワケ。

次のファイル名を取得してくれる仕組み
※17 次のファイル名を取得してくれる仕組み

ただし、Excelを順次開いていくと自分自身(=マクロ入りブック)も拾ってしまうので、「もし自分と同じファイル名だったら何もしない」ようif文で逃がしてあげます※18

ファイル名が自分と同じだったら、何もしない
※18 ファイル名が自分と同じだったら、何もしない

で、そうやって次々ファイル名をひろっていって、全部ひろい終えた=myFname2が空になるので、そのときループは終了する、と。
こういう具合になっています。

…うん、じゃあちょっとここらで、dir関数の特徴がわかるような練習用マクロを書いてみたので、動かしてみてください。 下記マクロを入れたブックを適当なフォルダに入れてマクロを動かすと、フォルダ内のファイル名を次々メッセージボックスに表示させることができます※19

フォルダ内のファイル名を取得する 拡張子を指定していないのでファイルなら何でもひろう
※19 フォルダ内のファイル名を取得する 拡張子を指定していないのでファイルなら何でもひろう

マクロの中身については、マクロ本文に詳細にコメントを残したのでそれを読んでいただければ挙動はわかるかな、と※20

詳細はコメントを読んでみてください
※20 詳細はコメントを読んでみてください

このサンプルは、
Dir関数を使うとファイル名を取得できる
■引数省略のDir()を使う:次のファイル名を取得
■引数を入れ直す:ふりだしに戻る
ってことをわかってもらうためのマクロです。 なので、その点が理解できればOK※21

Dir()で次々ファイル名が取得できるのさ
※21 Dir()で次々ファイル名が取得できるのさ

ということで、だいたい解説できた、かな?

1行目1列目から始まっていない表にも対応できるよう工夫する

じゃあ、だいたい説明は済んだかと思うので、次いっちゃうよ。 「ちょっとまだ不安だな…」って人は、マクロにコメントを残してあるので、そこもしっかり読んで、読み解いてみてください。

ということで、次。 さっきのマクロをちょっと改良して。
上のマクロは「1列目1行目から表がはじまっている」のが前提でした。 そこを、「2列目2行目から」とか「2列目3行目から」などに柔軟に対応できるようにしたのが下↓のマクロです。

たとえばこんなふうに、2列目2行目からはじまってる表でも…※22

はじまりが1列目1行目じゃなくても…
※22 はじまりが1列目1行目じゃなくても…

2列目から」「2行目から」とインプットボックスに入れてやれば、そのフォーマットで合体します※23 ※24

何列目何行目からはじまるのかをインプットボックスに入れてやれば…(この場合は2の2)
※23 何列目何行目からはじまるのかをインプットボックスに入れてやれば…(この場合は2の2)
指定のところからの表はじまりで合体できる
※24 指定のところからの表はじまりで合体できる

元のマクロからの主な変更点は下記↓※25 ※26

変更点1:変数追加とインプットボックス
※25 変更点1:変数追加とインプットボックス
変更点2:入力した変数が活かされるよう書き直した
※26 変更点2:入力した変数が活かされるよう書き直した

デフォルトの数値に「1」を設定してあるので、従来どおり1列目1行目からはじまる表でも合体できますよ。
(もちろん、全ブックが同じフォーマットでないとダメですよ。 「この表は1列目1行目から、次のは2列目3行目から…」ってのは無理です。)

おまけ:全ブックの全シートを巡回する

最後に。
各ブックにふくまれるすべてのシートを巡回して合体するマクロについても作ってみました※27 ※28

複数のシートがあっても…(シート名「やまだ」は意図的にやってます)
※27 複数のシートがあっても…(シート名「やまだ」は意図的にやってます)
全部巡回して合体してくれる
※28 全部巡回して合体してくれる

作り方は、前回の「シート合体マクロ」との合わせ技です。 要するに、ループ処理を2回行い、ブックを開くループ(大ループ)の中に、各シートを見に行くループ(小ループ)を入れ子にする、という※29

ループ in ループ
※29 ループ in ループ

いかがでしたでしょうか。
「ブックを次々開く」の仕組みが分かれば、これ以外にも、「ブックを次々開いて、印刷設定を行い、プリントする」とか「ブックを次々開いて、更新して、保存して閉じる」とか応用が利くと思いますので。
ではでは、シーユーアゲン!

Copyright(C)森田表計算