【Excel VBA】テーブルに別ファイルのデータをインポート

Excelのテーブルに「別ファイルのシートにあるデータ」をインポートし、テーブルのデータを更新するVBAサンプルの備忘録。

作成の背景は、Power Queryで同じファイル内のシート（テーブル）をデータソースとする管理をしたいが、元のデータ自体は別ファイルにあるため、テーブルのデータ更新を効率化。

同じファイル内のシートをデータソースにする理由は、データソースを含めたファイル共有を容易にしたかったため。

サンプルの前提

Excelのファイルに、シート名「元」があり、テーブル名「元データ」でデータが入力されている。
「元データ」テーブルについてテーブル状態を維持したまま、データ削除し、別のExcelファイルを都度選択してデータをインポートする。
別のExcelファイルには、シート名「購買履歴」があり、１行目にタイトルがある。列の順序は同じだが、行数はファイル毎に異なる。
シート名「クエリ後」は、テーブル名「元データ」をデータソースにしている。
使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」

VBAサンプル

VBAコードのサンプルは次のとおり。

Sub 元データのインポート()
    
    'テーブルにデータがあれば、テーブルの状態維持してデータ削除'
    Sheets("元").Select
    If Not Sheets("元").ListObjects("元データ").DataBodyRange Is Nothing Then
        Sheets("元").ListObjects("元データ").DataBodyRange.Delete
    End If

    'Excelファイルを開く'
    Dim ファイル名 As Variant
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "インポートするExcelファイルを選択"
        .Filters.Clear
        .Filters.Add "Excelファイル", "*.xlsx"
        If .Show = -1 Then
            ファイル名 = .SelectedItems(1)
            .Filters.Clear
        Else
            .Filters.Clear
            Exit Sub
        End If
    End With
    Workbooks.Open ファイル名
    
    '配列としてデータに入れる'
    Sheets("購買履歴").Select
    最終列 = Sheets("購買履歴").Cells(1, Columns.Count).End(xlToLeft).Column
    最終行 = Sheets("購買履歴").Cells(Rows.Count, 1).End(xlUp).Row
    配列データ = Range(Sheets("購買履歴").Cells(2, 1), Sheets("購買履歴").Cells(最終行, 最終列)).Value
    ActiveWorkbook.Close SaveChanges:=False
            
    ThisWorkbook.Activate
    Sheets("元").ListObjects("元データ").Range(2, 1).Resize(UBound(配列データ, 1), UBound(配列データ, 2)).Value = 配列データ
    
    Erase 配列データ
    
    ActiveWorkbook.RefreshAll'クエリ更新'
    
End Sub

サンプルの補足説明

データのコピー、貼り付けは、Range.Copy メソッドを利用でも可。他の目的で、VBA上で一旦データ加工を実施して貼り付けしたい場面もあるので、配列データで処理している。
Sheets("元").Selectなど、余分と思われるコードや冗長な部分もあるが、引継やデバッグのしやすさの点で、今回は残している。
サンプルは、ファイル選択形式としているが必要に応じて以下などアレンジ可能。
shikumika.org

以上、Excelのテーブルに別ファイルのデータをインポートし、テーブルのデータを更新するVBAサンプルでした。

また、PowerQueryのデータソースをどのように配置すると良いのかなどを以下にまとめてみたので、ご覧ください。

shikumika.org