VBAマクロでExcel複数ファイルを１つのファイルにまとめるやり方とTips

はじめに

・ビッグデータが複数個のファイルや様々なフォーマットで提出された場合、そのデータを整形し、一つの同一フォーマットファイルに整える必要が出て来きます。
・その際、VBAマクロを使えば場合、環境構築なく「マクロの記録」を行いながら初心者でも分かりやすくデータの整形ができるため、簡単な操作であればVBAマクロを使うのが早いと思われます。
・今回はその中で複数ファイルを一つのファイルにまとめる手法と実際に躓いたTipsを纏めました。

今回行った処置の目的

・納品されたファイルがエクセル形式（.xls）で複数ブック・複数シートにまたがっており、まずは一つのエクセルファイルに複数シートで統合する。
・その際、複数のエクセルファイルを同一フォルダに移行し、存在する一つ一つのエクセルファイルを検索・開き、開いているエクセルファイルのシートの末尾にコピーするといった処理を行う。

スクリプト

Sub consolid()
    Application.ScreenUpdating = False
    Set mb = ThisWorkbook
    myfdr = ThisWorkbook.Path
    fname = Dir(myfdr & "\*.xls")
    Do Until fname = Empty
    If fname <> mb.Name Then
    Set wb = Workbooks.Open(myfdr & "\" & fname)
    wb.Worksheets.Copy After:=mb.Sheets(mb.Sheets.Count)
    wb.Close
    n = n + 1
    End If
    fname = Dir
    Loop
    Application.ScreenUpdating = True
    MsgBox n & " object(s) is(are) copied."
End Sub

参考： http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11119773176

・３行目で現在開いているファイルを取得しています。
・4-5行目で現在開いているファイルと同じディレクトリにあるエクセルファイルを変数に突っ込んでいます。
・6行目からエクセルファイルを1つ1つ順場に開いて、現在開いているファイルの末尾にシートを付け足すという処理をしています。

トラブルシューティング

Macを使っている

・Macを使う場合dir関数を使うとバグが出ます。
・以下のように力技で行うこともできるようですが、私の環境では上手く動きませんでした。

Excel for mac 2011 VBAで，Dir関数っぽい動作をさせるプログラム例
http://chiroleen.hateblo.jp/entry/20130222/1361537144

・Windows版を使うのが手っ取り早いようです。

Windows English OSがダウンロードされている

・Workbook.Pathを指定してあげるときに日本語がディレクトリ名、ファイル名に使用されていると上手く行きません。（日本語が使われている部分が???で帰ってきます。）
・英語のみのディレクトリ、ファイル名にすると上手く動きます。

あとがき

・MacのVBAは他にもバグがあったり、ディレクトリの指定パスがWindowsと違うことが多いようです。
・VBAマクロをいじる時はWindows環境で実行することを強く勧めます。