Excelで別のブック(ファイル)からデータをコピーするマクロ

「別のブックからコピー マクロ」というキーワードで検索し、当ブログをご覧いただいています。
改めてそのパターンをまとめてみました。

開いている別のExcelブックからコピーするマクロ

Excelの別のブック(ファイル)からデータをコピーするには、
・Ctrl+C(コピー)
・Ctrl+Tab(ブックの切り替え)
・Ctrl+V(貼り付け)
を使ったとしてもそれなりの手間です。
複数のブック、それを繰り返すとなると、やってはいけません。

数式で連動したとしても、ブックが増えたときに困ります。
こういったときは、マクロを使わざるを得ません。
(マクロを使わないなら、複数のブックにわけないような工夫をしたいものです)

そのマクロ。
1つの方法は、元のブックを開き、その開いたブックからデータをコピーするものです。

このように開いておき、
・セルA2に、経費精算書のセルE4
・セルB2に、経費精算書のセルE6
を入れます。

○=△
と書くと、○に△を入れることができますので、
セルA2にブック「経費精算書」のセルE4を入れるなら、

セルA2=ブック「経費精算書」のセルE4

と書きます。

セルの指定は、Rangeです。
Excelは、ブック→シート→セルという構成であり、セル(Range)だけを書くと、今選択しているブック→今選択しているシートのセルという意味になります。

Range(“a2”).Value = Range(“a7”).Value
だと、セルA2にセルA7を入れる、セルA7をセルA2にコピーするという意味です。
コピーするマクロは、Copyというものがありますが、これを使うと、書式までコピーされてしまいます。
そのデータ(値)だけコピーするなら、○=△のほうが好ましいです。

また、1つのブックで操作するなら、Rangeだけでもいいのですが、複数のブックを操作するマクロなら、ブック→シート→セルの指定をしたほうがミスは減ります。

ここでは、
ブック→このマクロが入っているブック→ThisWorkbook
シート→選択しているシート→ActiveSheet
セルA2→Range(”A2″)
を使いましょう。

コピー元は、
ブック→このマクロが入っているブック→Workbook(“ファイル名”)
シート→選択しているシート→ActiveSheet
セルE4→Range(”E4″)
です。
※セルの指定は、小文字でも大文字でもかまいません。

ブックの指定は、ブック名のみ(植村 経費精算書.xlsx)にしなければいけません。

一方、ブックを開くところからマクロをつくることでもできます。

別のExcelブックを開いてコピーするマクロ

Excelブックを開くなら、
Workbooks.Open (ブック名)
と書きます。

ブック名は、このエクスプローラーで、[パスのコピー]をした状態のもの(フルパスといいます)です。

Workbooks.Open (“D:\Dropbox\経費データzzz\植村 経費精算書.xlsx”)
と指定します。

さらにセルE6のコピーも加えてみました。
ブックを開き、その「選択しているブック」という意味で、ブックの指定は、ActiveWorkbookにします。

ブックを開くところからマクロにしたほうが実用的です。
フォルダにあるすべてのブックからコピーする場合にも使えます。

フォルダにあるすべての別のブックからコピーするマクロ

複数ある別のブックからコピーするには、「フォルダにあるすべてのブックからコピー」するマクロを使ったほうが便利です。

こういったものを書きます。

Sub macro()

'ブックがあるフォルダの指定
    Dim Folder_path
    Folder_path = "D:\Dropbox\経費データzzz"
    
'ブックの指定
    Dim MergeWorkbook
    MergeWorkbook = Dir(Folder_path & "\*.xlsx")
   
'集計先の行
   
   Dim r
   r = 2
    
'指定したフォルダから、ブックを探す
    Do Until MergeWorkbook = ""
        
'ブックを開く
        Workbooks.Open Filename:=Folder_path & "\" & MergeWorkbook
        
'データを転記
        ThisWorkbook.Worksheets("data").Range("a" & r).Value = Workbooks(MergeWorkbook).ActiveSheet.Range("e4").Value
        ThisWorkbook.Worksheets("data").Range("b" & r).Value = Workbooks(MergeWorkbook).ActiveSheet.Range("e6").Value
        
        r = r + 1  '次の行へ
           
        '集計するブックを閉じる
        Application.DisplayAlerts = False
            Workbooks(MergeWorkbook).Close
        Application.DisplayAlerts = True
    
        '次のブックを探しに行く
        MergeWorkbook = Dir()
    Loop

     

End Sub


まず、フォルダを指定し(パスのコピー)、ブックを指定します。 フォルダにある「.xlsx」という種類のファイルを設定するのです。 (厳密には、.xlsもあるので、「.xls*」と指定したほうが無難ですが)

'ブックがあるフォルダの指定
    Dim Folder_path
    Folder_path = "D:\Dropbox\経費データzzz"
'ブックの指定
    Dim MergeWorkbook
    MergeWorkbook = Dir(Folder_path & "\*.xlsx")
    

集計先のデータは2行目からはじまっているので、rとして、2と初期値を設定します。 これを、3、4、5……と1つずつ増やし、1行ずつデータを入れていくわけです。 そして、フォルダからブック(Excelファイル)を探し、見つかったら、開いて、転記していきます。

'集計先の行
   
   Dim r
   r = 2
    
'指定したフォルダから、ブックを探す
    Do Until MergeWorkbook = ""
        
'ブックを開く
        Workbooks.Open Filename:=Folder_path & "\" & MergeWorkbook
        
'データを転記
        ThisWorkbook.Worksheets("data").Range("a" & r).Value = Workbooks(MergeWorkbook).ActiveSheet.Range("e4").Value
        ThisWorkbook.Worksheets("data").Range("b" & r).Value = Workbooks(MergeWorkbook).ActiveSheet.Range("e6").Value
        
        r = r + 1  '次の行へ
           

最後に、開いたブックを閉じるという処理も入れました。

           
        '集計するブックを閉じる
        Application.DisplayAlerts = False
            Workbooks(MergeWorkbook).Close
        Application.DisplayAlerts = True
    
        '次のブックを探しに行く
        MergeWorkbook = Dir()
    Loop

それぞれのブックのすべてのシートという指定もできます。 こちらの記事を参考にしてみていただければ。
フォルダ内のExcel・CSVを1つにまとめる(結合する、マージする)マクロ

また、取得と変換(Power Query)を使う方法もありますので、こちらの記事を参考にしてみていただければ。
フォルダ内の複数のExcelファイルを結合する「取得と変換」(Power Query)



■編集後記
昨日は、ラン→スイム→午後はWordPressブログ入門セミナーを開催しました。
夜は、テイルズオブアライズを。

1日1新Kindle『1日1新』Instagram『1日1新』
田町 Boulangerie la Demande

■娘(5歳)日記→Kindle『娘日記』・ Kindle『娘日記Ⅱ』
夜は、アベンジャーズ(エイジオブウルトロン)を。
続きのシビル・ウォー、あらすじを話すと、楽しみにしているようです。
その続きは、見たことがあるのですが。