【VBA】xlsxファイルを開いて内容をコピーする

複数あるExcelファイルを一つにまとめたいときありますよね。シート一個一個手作業でコピーできますが、VBAで自動化できます。シートのコピーではなく、セルのコピペでもありません。Excelファイルの内容をVBA変数に入れて該当シートに出力します。こちらのほうが高速です。

下記ソースは1ファイルしか対応していません。関数化してFor Each で回してください。

プログラムの流れ
プログラム
解説
結果

プログラムの流れ

ファイル選択画面を開いてファイルを選ぶ
選んだエクセルファイルのデータを取得する
取得したデータをシートに張り付ける

プログラム

関数でプログラムを書いています。

Public Function CopyXLSXData()
    
    Application.ScreenUpdating = False

    'ファイル選択ダイアログを開く

　　openFilePath = Application.GetOpenFilename("Excelファイル, *.xlsx")
    
    If OpenFileName = False Then
        Exit Function
    End If
    
    Dim wb As Workbook
    Dim ws As Worksheet
    
    'ワークブックを開く
    Set wb = Workbooks.Open(OpenFileName)
    
    'シートを取得
    Set ws = wb.Worksheets(1)
    
    'データ入力されている範囲を取得する
    ExcelData = ws.UsedRange
    
    'ワークブックを閉じる
    wb.Close
    

    Set ws = Nothing
    Set wb = Nothing

    
    With Sheets("いったんこのシートに保存")

　　　　'すべてをクリアにする
        .Cells.Clear
        
        Dim MaxRow As Long
        Dim MaxCol As Long
    
        'データの最大行を取得する
        MaxRow = UBound(ExcelData, 1)
        
        'データの最大列を取得する
        MaxCol = UBound(ExcelData, 2)
        
        'セルA1を基点に取り込んだデータを出力する
        .Cells(1, 1).Resize(MaxRow, MaxCol).Value = ExcelData
    End With
    
    
    Application.ScreenUpdating = True
    
    
End Function

解説

ファイル選択ダイアログを出して該当するファイルを選びます。

openFilePath = Application.GetOpenFilename("Excelファイル, *.xlsx")

本当は.xlsxファイルだけでなく.xlsファイルにも対応しているとすべてのExcelファイルが開きます。ですが今回はxlsxだけ開けば問題はありませんのでこれでいきます。

シートで使用されているセルの範囲全てを取得するには

ExcelData = ws.UsedRange

を使用します。1行ですむので大変便利な関数です。

データをシートに張り付ける方法としてCopy関数・Paste関数などを使用することもできますが、データでやりとりしたほうが高速化されます。ですので、以下の方法で行いました。

.Cells(1, 1).Resize(MaxRow, MaxCol).Value = ExcelData

Resize関数はその名の通り指定範囲の大きさを変更します。Cells(1,1) つまり A1 からデータのある最大行・最大列までにデータを入れます。

結果

無事にデータをシートに取り込むことができました。すべて左寄りになってます。コピー元で結合されていたセルは解除されます。