【Excel VBA】ブックを開かずにシートの内容を高速取得する

Excelブックが重く、読み込みに時間がかかるということはありませんか?

実は重いブックでも、PowerQueryを利用することでシートの内容を高速で読み込めます。

今回はその方法について説明していきます。

ブックを開かずにシートの内容をコピーする
1. シートの内容をコピーするメソッド
  1. 前提条件
  2. 制約
2. サンプルコード
速度比較

ブックを開かずにシートの内容をコピーする

シートの内容をコピーするメソッド

PowerQueryを利用して、Excelブックを開かずにシートの内容をコピーするメソッドです。

出力シートへ参照シートの内容を全てコピーします。

'---ブックを開かずにシート内容をコピーする---
'filePath:参照ブックのファイルパス
'sheetName:参照シート名
'outSheetObj:出力するシート
'------------------------------------------
Sub CopySheetFromBook(ByVal filePath As String, ByVal sheetName As String, ByRef outSheetObj As Worksheet)
    'PowerQueryのクエリ文を生成
    ActiveWorkbook.Queries.Add Name:="sheetItem", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""" & filePath & """), null, true)," _
        & Chr(13) & "" & Chr(10) & "    sheetItem_Sheet = Source{[Item=""" & sheetName & """,Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & _
        "    Header = Table.PromoteHeaders(sheetItem_Sheet, [PromoteAllScalars=true])" _
        & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Header"
    'PowerQueryを実行
    With outSheetObj.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=sheetItem;Extended Properties=""""" _
        , Destination:=outSheetObj.Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [sheetItem]")
        .ListObject.DisplayName = "sheetItem"
        .Refresh BackgroundQuery:=False
    End With
    'PowerQueryの接続を切断
    ActiveSheet.ListObjects("sheetItem").Unlist
    'テーブルを解除
    ActiveWorkbook.Queries("sheetItem").Delete
End Sub

前提条件

ブックを開かずにシートの内容をコピーするには、参照するシート名が決まっている必要があります。

参照するシート名が決まっていない場合は、ブックを開かずにシート名を取得する関数と組み合わせることでシート内容のコピーが可能です。

制約

PowerQueryを利用するため、主に以下の制約が発生します。

書式が反映されない
結合セルか判定できない
セルサイズが出力先に依存する
最初の列より前の空欄の列は読み込まれない。例えば1～2列目の列が空欄で3番目の列からデータが入っている場合、1～２番目の列が読み込まれない。

サンプルコード

対象ブックのパスとシート名を入力すると自動的にコピーされるマクロです。

'指定されたブックのシートデータをコピーする
Sub Sample()
    Dim outSheetObj As Worksheet
    Set outSheetObj = Worksheets.Add
    Dim filePath As String
    filePath = InputBox("ブックパスを指定してください")
    If filePath = "" Then Exit Sub
    Dim sheetName As String
    sheetName = InputBox("シート名を指定してください")
    If sheetName = "" Then Exit Sub
    Call CopySheetFromBook(filePath, sheetName, outSheetObj)
End Sub

速度比較

ブックを開いた場合と開かない場合の速度を比較しました。

使用したデータは、個人情報テストデータジェネレーターで作成した1万件のデータを50枚のシートに転記したブックを使用しました。

比較に使用したコードは以下の通り。開かない場合のコードは「ブックを開かずにシートの内容をコピーする」のサンプルコードと同じです。


'ブックを開いた状態でシートをコピーする
Sub Test1()
    Dim outSheetObj As Worksheet
    Dim filePath As String
    filePath = InputBox("ブックパスを指定してください")
    Dim sheetName As String
    sheetName = InputBox("ブックパスを指定してください")
    If filePath = "" Or sheetName = "" Then Exit Sub
    With Workbooks.Open(filePath)
        .Worksheets(sheetName).Copy After:=ThisWorkbook.Sheets(1)
        .Close
    End With
End Sub

開いた場合 7秒
開かない場合　1秒

速度を比較した結果、開かない場合の方が７倍ほど高速なようです。