VBA編其ノ12 複数のシートのデータをひとつに合体 後編
ハイ、前回の続きです。
前回は、シートを1コずつおしりまで見に行くのと、コピペの仕方を学んだんでしたね。
サンプルデータは前回のものを今回も使いますよ。
では本日は、新しいブックの開き方から。
新しいブックを開く、のだが…
さっそくですが、新規ワークブックを開くワードはworkbooks.addです※1。
やっぱり今回も「workbooks」と複数形です。 「add」は「加える」という意味。 まっさらなExcelを開くときには、「新しいブックを追加する」という考え方をするみたいですね、Excelは。 ちなみに、「open」というワードもあって、そちらは「すでに存在するファイルを(ファイル名を指定して)開く」ときに使います。
さて、ではworkbooks.addの挙動を確かめてみましょう。 マクロ専用ブックに「workbooks.add」と書いたら、いつものように、サンプルデータとマクロブックの両方を開いて、サンプルデータの方からマクロを動かしてみてください※2。 新しいブックが開いて、Excelが計3コになります※3。
新規ブックを開くことができました。
…しかし、コレ、ブックを切り替えるのはどうやるんだ?
つまり、現状、
1. 新規のまっさらなブック
2. sample_v11.xls(ダウンロードしてもらったサンプルデータ)
3. マクロ入りブック
の3つがありますよね。
で、コピペするには、1. と2. を往復しなければならないんです。
手作業だったら、マウスなり、Ctrl+Tabなりで切り替えてやればよいのですが、…コイツをVBAでやるには、どうしたら…?
名前をつけて(変数に格納して)、切り替える
ハイ。
結論から言うと、変数を使います。
変数を使って名前をつけてやると、名指しで作業を指定してやることができます。
ためしに、下↓のマクロを動かしてみましょう※4 ※5。
sample_v11.xlsの方からマクロを動かすと、sample_v11.xlsと新しく追加したブックを、交互に3回切り替えます。
ハイ、では中身の解説。
今回、ブックを切り替えるのに、変数を2つ使います。
それが、最初に宣言した「oldBK」と「newBK」です。
変数の名前は何でもよいんだけど、わたしがわかりやすいようにこうしています。(厳密に言うと、元データの方を「古いブック」と呼ぶのはいまいちハマってない気もするけど、…まあ、いいじゃない。)
でね、この2つの変数に、sample_v11.xlsと、新しく追加したブックを割り当てる、というのが、今回のキモなんですよ※6。
まず形式的かつおさらい的な話をしておきたいんですが…、はじめの「変数の宣言」のところ、「Dim oldBK As Workbook」「Dim newBK As Workbook」となっていますね。 変数「oldBK」「newBK」にはワークブックを格納したいので、workbookオブジェクトとして宣言します。 そして、変数にオブジェクトを格納するときはアタマに「Set」をつけるんでしたね。 この辺りの話は其ノ9でやりました。 思い出しておいてください。
で、「activeworkbook」というのは文字通り「アクティブなワークブック」の意味です。
つまり、一番手前にあって、入力を受け付けているブックのことです。
今回のケースで言うと、われわれはsample_v11.xls上でマクロを動かした、すなわちsample_v11.xlsがアクティブだったので、sample_v11.xls = activeworkbookということになります。
で、そこに、「oldBK」という名前を付けた、ってワケ。
同様に、新しく追加したブックに「newBK」と名前を付けました。
で、その2つを、「.activate」というメソッドで切り替えている、というワケなんです。
「activate」は「アクティブにする」くらいの意味です。
ブックを切り替えるのに「.select」は使えないことに注意してください。
「なんで?」と申されましても…、「そういうものだ」と理解してください。
逆に、シートを選択するときには、「.select」も「.activate」も、両方ともほぼ同じ意味で使えます※。
「アクティブにする」ってことは、「選択する」ってことですからね。
※シートに対してなら、両者はほぼ同じように使えます。 ただ、1点違いを挙げておくと、.selectの方は「シート1・2・3をまとめて選択」という使い方もできます。 …アクティブにできるのって、1コだけですもんね。
ただ、ここでひとつ注意。
workbooks.addは、実行すると、新しく出来たブックが強制的に一番手前にくる(=アクティブになる)仕様になっています。
だから、もし変数に格納する命令部分
○Set oldBK = ActiveWorkbook
○Set newBK = Workbooks.Add
をまちがえて、
×Set newBK = Workbooks.Add
×Set oldBK = ActiveWorkbook
と逆にしてしまうと、このマクロはブックを切り替えられなくなってしまいます。
なぜなら、activeworkbookが「アクティブなワークブック」である以上、2コ目の変数「oldBK」に結び付けられるのは新規追加ブックになってしまうからです。
…ちょっとまとめておきましょう※7。
要するに、activeworkbookというのは相対的な概念なんですね。
その後の操作で、どのブックがアクティブなのかは変わってくる。
だから、逆に言えば、acitiveworkbookの指し示すところが変わっちゃう前に、アクティブなブック(=sample_v11.xls)に「oldBK」という名前を付けておこう、というワケなんです。
…う~ん…、なんだか話がややこしくなってしまいましたね。
まあ、あの、とりあえず一回このかたちで覚えちゃいましょうか。
使っていくことで徐々に理解がついてくるということもありますし。
…あ、あと、Application.Wait Now + TimeValue("00:00:01")は、1秒待つときの定型文だと思ってください。
「Now + TimeValue("00:00:01")」で、「今の時刻に1秒プラスした時刻まで(待つ)」という命令です。
…まあ、実際仕事用のマクロで使うことはないので、覚える必要はないですケド。
と、いうことで、これで今回のマクロは書ける、かな?
いよいよ複数のシートのデータをひとつに合体してみる、刻はきた、…それだけだ
では、これまでやったことを組み合わせて、マクロを書いてみましょう。
コイツを動かすと…※8 ※9。
お、うまくいってるみたい。 よしよし。
では、3点注意。
■例によって例のごとく、セル(1, 1)から表が始まっている&1列目(最左列)がみっちり空白なく埋まってることが前提になっています(最終行の取得に最左列を使っているので、空白があったりすると困るんですよ)。
なので、みなさんのお仕事に応用する場合は、この部分各自適宜調節願いたく。
■必ずsample_v11.xlsの方から、つまり合体させたいブックの方からマクロを動かしましょう。
「マクロ起動時にどのブックがアクティブなのか」ということが、このマクロにとって重要だからです。
まあ、今までのマクロもそうですけど。
■このマクロに限った話ではありませんが、マクロを動かしている最中は触らないようにしましょう。
Excelに触れたり、他のアプリケーションを操作したりしないように。
よけいな動きが何か悪影響を及ぼすかもしれません。
触ってよいのはEscで強制退出するときだけ、です。
落ち着きのない方は特に心がけておきましょう。
では、解説。 と言っても、これまでのところでほとんど説明しちゃったので、かんたんに※10 ※11。
このマクロのメインはループ処理にあるのですが、そこに入る前に、まずヘッダー(項目名)部分だけ作ってしまいます。 どのシートからもってきてもよい(全シート同じフォーマットであることが前提なので)のですが、さしあたりシート1からコピーしてくることにしました。
このマクロでは最終行を2つ取得します。
元データ(oldBK)の最終行と、新規ブック(newBK)の最終行です。
それぞれ、変数LastRw1、LastRw2に格納します。
新規ブックのデータ量は合体のたびに増えていくので、LastRw2はその都度取得し直します。
また、LastRw1の方も、シートを切り替える度に取得します。
そりゃそうですよね、シート毎に件数はちがうのですから。
このように、oldBKとnewBKの最終行の位置をその都度確認しながら、このマクロは進んでいくのです。
…このマクロの動きを手作業で再現すると、こんな感じ※12~※17です。
こんな感じ。
最終行の取得がポイントだってこと、わかったかな?
もうちょいブラッシュアップ
上でだいたいの仕組みは出来上がったんだけど、もうちょっと使い勝手をよくしたいなと思ったので、改良してみました。
改良点は3つ。
まず、非表示の列を表示するようにしたこと。
また、フィルタがかかっていたら解除(全件表示)するようにしたこと※18。
作業する際、列を非表示にしたり、フィルタをかけたりすること、よくありますが、そのまま合体しちゃったらまずいですもんね。
なので、この改良版ではその問題に対応しています。
あと、小さいことですが、合体後の件数を表示するようにしました※19。
最後に、マニュアル作ってやれば終了です※20。 お疲れ様でした。
もしみなさんの仕事に使えそうであれば、使ってやってください。