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

2022年6月9日

URLをコピーしました！

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

1 開いている別のExcelブックからコピーするマクロ
2 別のExcelブックを開いてコピーするマクロ
3 フォルダにあるすべての別のブックからコピーするマクロ

開いている別の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）