【EXCEL：VBA】他のEXCELファイルのデータを取り込みたい

この記事では、VBAを使って他のEXCELファイルのデータを読み込み、自EXCELのシートに貼り付ける機能の作り方を説明します。

読み込みするEXCELファイルのシートを指定可能、貼り付け先のEXCELファイルのシートも指定可能となっています。

このVBAはいろいろな便利な機能を作るときに、利用できますのでぜひご覧ください。

困っている女性

他のEXCELファイルのデータを簡単に取り込む方法を知りたいな・・・。

VBAで他のEXCELファイルのデータを取り込む自作関数を作ればいいよ。

解決する男性

困っている女性

本当？それって簡単？

簡単だよ。いますぐ使える！！

解決する男性

困っている女性

それじゃあ、早く教えてよ。

◆似たような作業で、指定されたフォルダに存在するすべてのCSVファイルを一気に取り込みする方法については、以下の記事を参照してください。

2020-12-26【EXCEL：VBA】フォルダに存在するCSVファイルを全て取り込みたい

◆似たような作業で、単純に1つのCSVファイルを取り込みする方法については、以下の記事を参照してください。

2020-06-23【EXCEL：VBA】CSVファイルを取り込みたい

それでは、他のEXCELファイルを取り込む機能の作り方を順番に説明していきます。

1．取り込まれるEXCELファイル（xlsx）を準備する

なにはともあれ、取り込まれるEXCELファイルを準備します。

取り込み対象のEXCELファイル

適当なファイル名でEXCELを作成し、「Sheet1」にデータを入力しておきます。

2．取り込み機能を実装するEXCELファイル（xlsm）を準備する

VBAを記述しますので、xlsm形式でEXCELファイルを作成します。

取り込むEXCELファイルを指定するセルを用意する

「Sheet1」に以下を指定するセルを用意します。

取り込むEXCELファイルのパス（セルB1）
取り込むEXCELファイルのデータが存在するシートNO（セルB2)
取り込んだデータを出力するシートNO（セルB3）
取り込んだデータを出力を開始するセル（セルB4）

3．EXCEL取り込み機能をVBAで作成する

「取り込むEXCELファイルのパス」と「取り込むEXCELファイルのデータが存在するシートNO」を引数に持つEXCEL関数を作成します。

EXCELデータ取り込み関数

Public Function GetExcelData(ByVal FilePath As String, ByVal SheetNo As Integer) As Variant
    Dim wb As Workbook
    Dim ws As Worksheet
    
    'ワークブックを開く
    Set wb = Workbooks.Open(FilePath)
    
    'シートを取得
    Set ws = wb.Worksheets(SheetNo)
    
    'データ入力されている範囲を取得する
    GetExcelData = ws.UsedRange
    
    'ワークブックを閉じる
    wb.Close
    
    'メモリー開放
    Set ws = Nothing
    Set wb = Nothing
    
End Function

Public Function GetExcelData(ByVal FilePath As String, ByVal SheetNo As Integer) As Variant

Dim wb As Workbook

Dim ws As Worksheet

'ワークブックを開く

Set wb = Workbooks.Open(FilePath)

'シートを取得

Set ws = wb.Worksheets(SheetNo)

'データ入力されている範囲を取得する

GetExcelData = ws.UsedRange

'ワークブックを閉じる

wb.Close

'メモリー開放

Set ws = Nothing

Set wb = Nothing

End Function

上記の関数は、EXCELファイルから取り込んだデータを配列に格納して返します。

次に、先程のEXCELデータ取り込み関数を利用する関数を作成します。

EXCELデータ取り込み関数を利用する関数

Public Sub Test1()
    Dim var As Variant
    Dim FilePath As String
    Dim InSheetNo As Integer
    Dim OutSheetNo As Integer
    Dim OutCell As String
    
    With ThisWorkbook.Sheets(1)
        FilePath = .Range("B1")
        InSheetNo = .Range("B2")
        OutSheetNo = .Range("B3")
        OutCell = .Range("B4")
    End With

    
    '他のEXCELファイルデータを取り込む
    var = GetExcelData(FilePath, InSheetNo)
    
    Dim MaxRow As Long
    Dim MaxCol As Long
    
    '最大行数を取得する
    MaxRow = UBound(var, 1)
    
    '最大列数を取得する
    MaxCol = UBound(var, 2)
    
    '自ワークブックの1番目のシートのセルA1を基点に取り込んだデータを出力する
    ThisWorkbook.Sheets(OutSheetNo).Range(OutCell).Resize(MaxRow, MaxCol).Value = var
    
End Sub