エクセルとVBAを上手に使う①他のファイルからの貼付

エクセルを上手に使う第一歩は、「なるべくワークシートで処理すること」「なるべくマクロを書かないこと」。これを意識するだけで出来上がったツールのわかりやすさがだいぶ違う。
そのためにどうするかと言うと、基本的には、

1. ツールの中心となるブックを用意（これを「ハブ」と呼ぶことにする）
2. 必要なファイル（エクセルとは限らない）のデータを全てハブに貼り付け
3. ハブに作業シートを作って、貼り付けたデータを参照し、ワークシート関数だけで処理
4. ハブに出力用のシートを作って、作業シートの値を参照し、出力したい形に整形
5. 出力用シートを切り離して出力データを作成（必要ならワードファイルなども生成）

そんなわけで、マクロを書くとすれば、この内の「他のファイルからの貼り付け」「出力用の切り離し」が主なものになる。

今回は、まず「他のファイルからの貼り付け」について書く。

#マクロを書く前に
次のコードをメインとなるプロシージャの最初と最後に書いておく。

最初

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

最後

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

最初に書くほうは、処理を早くするための三点セットで、順に、画面再描画の抑止・自動イベントの抑止・再計算の手動化。最後のは逆にこれらを元に戻す命令である。
場合によっては、シート削除時のアラートをスルーさせる命令として
.DisplayAlert = false
も最初に入れておいたほうがいいかもしれない。
あるいは作業終了後に、コピペの名残である「点線囲み」が残るのが気持ち悪い（気持ち悪いだけでなく、これを残しているとブックを閉じる時にアラートされるのが困る）と感じるならば、
.CutCopyMode = false
を最後に入れるとよい。いずれもWith Applicationブロック内。

#ブックからブックへの貼り付け
他のブックを開いてハブに貼り付けるには、以下の書き方がオススメ。

ブック貼り付け

With Workbooks.Open("貼り付けたいブック.xls")
    '「貼付元」シートを全部コピー
    .Worksheets("貼付元").Cells.Copy
    'このブックの「貼付先」シートへ値貼り付け
    ThisWorkbook.Worksheets("貼付先").Range("A1").PasteSpecial _
        xlPasteValuesAndNumberFormats
    'コピー中状態を解除
    Application.CutCopyMode = false
    'ブックを保存せずに閉じる
    .Close False
End With

何故このようにWithを使うかと言うと、無駄にブックを参照する変数を生まなくて済むからだ。これは単に宣言の省略、あるいは一行の長さを短くするだけに留まらず、「何か不測の事態があった時にブックへの参照が永遠に残る」という事態を防ぐ意味もある。
VBAではブックへの参照を残したまま処理が終わると、最悪の場合エクセルを終了しても裏でそのブックを開き続けている（プロセスが生き残る）という現象が起こる。そういう意味でも上記の書き方がオススメである。

細かい部分では、値貼り付けの際にxlPasteValuesAndNumberFormats（値と形式の貼り付け）を用いている。これは、

• 普通にペーストするのは参照数式が残ってしまうから勿論だめ
• 単なる値貼り付けでは、古いエクセルの場合バグで貼り付けられない場合がある（結合セルが絡む場合）

という配慮による。もし新しいエクセルしか使わないことが決まっているなら、xlPasteValuesでよい。

なお、こうやって貼り付けた場合、「貼付先」シートに存在していた一切の値・数式は消える（たとえ貼り付けられるデータ領域の外側にあったとしても消える）。
これを防ぐには、シート全体のコピペではなく、特定の範囲だけを貼り付けるようにすればよく、その方法も色々あるにはあるのだが、諸々ややこしいのでやめた方がいい。
つまり、貼り付け先はあくまで貼り付け先としての役割以上を持たせないことがコツである。もし貼り付けたデータを加工したいなら、加工用のシートを別に持たせたほうがよい。

#アクセスDBからブックへの貼り付け
これは色々方法が分かれるが、私の場合は以下のように書いている。
なお、参照設定で「Microsoft DAO Object Library」の指定が必須であり、ついでに「Microsoft Access Object Library」も指定しておいたほうがいい。

DB貼り付け

With CreateObject("Access.Application") 'アクセスを開く
    '開くDBのパスを指定
    .OpenCurrentDatabase "開きたいDB.mdb"
    With .CurrentDb 'これで参照がDBに入る
        With .QueryDefs("貼付元クエリ") 'これで参照が指定したクエリに入る
            '必要なパラメータを入力
            .Parameters("パラメータ名") = "入力パラメータ"
            'クエリを実行し、このブックの「貼付先」シートへ貼り付け
            ThisWorkbook.Worksheets("貼付先").Range("A2").CopyFromRecordset _
                .OpenRecordset
            'クエリを閉じる
            .Close
        End With
        'DBを閉じる
        .Close
    End With
    'アクセスを閉じる
    .Quit
End With

やはり執拗にWithを使うのは、上で書いた理由の通りである。
なお、このマクロではクエリを貼る方法に限定しているが、そもそもテーブルそのものを開いて貼る、ということは考えない方がよい。ツールに対して必要なクエリを最初から作っておくのがコツである。もちろん、細かい操作はハブとなるブック側でできるので、必要最低限の抽出・リレーション・並び替えだけを行うクエリでじゅうぶん。

細かい部分では、貼り付け先のセルが「A2」となっている。
これは、CopyFromRecordsetでは列タイトルまで貼り付けないので、あらかじめ貼り付け先のほうに列タイトルを用意しておくようにしているからだ。
このようにアクセスDBからデータを貼り付ける時は、ブック同士の連携と異なり、貼り付けた範囲以外の場所にある値や数式を残すことができるのは重要な特徴だ。

#ワード文書からブックへの貼り付け
文書全体をコピーして貼り付けるというマクロは可能ではあるが、そもそもそんなことをする場面はまずありえない。
文書のフォーマットが余程整っていて、ルールが一貫していて、使いたいデータが独立して取り出しやすい形になっていて、その上で面倒な加工を沢山ほどこせば使用することもできるかなという感じだ。

むしろ有り得そうなのは、文書中の表をコピーしてきてエクセルで使いたい、という場合だろう。
これは次のように書ける。
（参照設定で「Microsoft Word Object Library」を指定しておこう）

ワード文書の表を貼り付け

With CreateObject("Word.Application")
    With .Documents.Open("貼り付けたいドキュメント.doc")
        '最初の表をコピー
        .Tables(1).Range.Copy
        'このブックの「貼付先」シートへ貼り付け
        With ThisWorkbook.Worksheets("貼付先")
            .Paste .Range("A1")
        End With
        'ドキュメントを保存せずに閉じる
        .Close False
    End With
    'ワードを閉じる
    .Quit
End With

なお、このマクロは文書中に表がただひとつしかない場合を想定している。
複数の表がある場合はTablesの引数を調整しなければいけない。

#貼り付けた結果を加工する
最初に書いたように、貼り付けたデータの加工はワークシートで行う。ひとつひとつのデータをマクロで加工すると色々と良いことが無い。一度でもやったことがあれば、わかってもらえると思う。

とはいえ、ワークシートでの加工には難点もある。
ワークシートで加工するには、そのための数式を初めからセルに置いておくことになるが、貼り付けることになるデータの行数がわからない場合には、その数式を何行目まで用意しておけばいいかがわからないのだ。
だからといって、エクセルの限界行数まで数式を置いておくのは愚の骨頂。
そこで解決策として次の方法をとる。

1. 数式は最初の一行目だけに置いておく
2. データを貼り付けたあと、その行数を確認する
3. 一行目の数式をオートフィルで貼り付けた行数ぶんまで増やす

もちろん、1以外の工程はマクロで実行することになる。
行数の確認については色々な方法があるが、Cells(Rows.Count, 1).End(xlUp).Rowなどでいいだろう（参考：ワークシートの最終行、最終列を取得する）。
とにかく何らかの方法で取得した「行数」の値をrowCountとでも名付けた変数に入れておく。
肝心のオートフィルは、

With ThisWorkbook.Worksheets("加工シート").Range("A1:Z1")
    .AutoFill .Resize(rowCount), xlFillCopy
End With

のようにすれば簡単だ（Rangeの範囲はツールにあわせて調整しよう）。

以上で、必要なデータを各所から収集し、ワークシートを使って加工する準備が整った。
実際の加工のポイントと、出力データを作成する作業は別枠で書こうと思います。