【Excel】ブックを開かずにシート名を取得する

プログラミング

2022.02.28

ブックを開かずにシート名を取得する方法について説明していきます。

ブックを開かずにシート名を取得する
1. コード

ブックを開かずにシート名を取得する

コード

PowerQuery（パワークエリ）を使用して、ブックからシート名一覧を取得するコードです。

'---ブックを開かずにシート名を取得---
'bookPath:Excelブックのパス
'返却値:成功時:シート名一覧(1次元配列),失敗時:Empty
Public Function GetSheetNamesFromBook(ByVal bookPath As String) As Variant
    'Excel設定を変更
    Application.ScreenUpdating = False '再描画停止
    Application.DisplayAlerts = False  '警告メッセージ非表示
    'クエリをブックに登録
     ActiveWorkbook.Queries.Add Name:="シート一覧", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    ソース = Excel.Workbook(File.Contents(""" & bookPath & """), null, true)," & Chr(13) & "" & Chr(10) & _
        "    フィルターされた行 = Table.SelectRows(ソース, each ([Kind] = ""Sheet""))," & Chr(13) & "" & Chr(10) & _
        "    削除された列 = Table.RemoveColumns(フィルターされた行,{""Hidden"", ""Item"", ""Data""})" & _
        Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    削除された列"
    'クエリを実行
    Dim sheetObj As Object
    Set sheetObj = ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=シート一覧;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [シート一覧]")
        .ListObject.DisplayName = "シート一覧"
        On Error GoTo Err
        .Refresh
        On Error GoTo 0
    End With
    ActiveWorkbook.Queries("シート一覧").Delete  'クエリを削除
    '結果を返却
    With sheetObj
        GetSheetNamesFromBook = WorksheetFunction.Transpose(.Range(.Cells(2, 1), .Cells(.Cells(1, 1).End(xlDown).Row, 1)))
        .Delete 'シートを削除
    End With
    'Excel設定を変更
    Application.ScreenUpdating = True '再描画開始
    Application.DisplayAlerts = True  '警告メッセージ表示
    Exit Function
    
    'クエリ失敗時の処理
Err:
    'シートを削除
    sheetObj.Delete
    On Error GoTo 0
End Function