シート名、名前付き範囲の一覧を取得する

シート名、名前付き範囲の一覧を取得する

Sub シート名、名前付き範囲の一覧を取得する()
Dim myCon As New ADODB.Connection, myRS As New ADODB.Recordset, FileName As String
Dim i As Integer
FileName = ThisWorkbook.Path & "\xls\DataBook.xls"
myCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;" & _
           "Data Source=" & FileName

'Excelブックのスキーマ情報を取得
Set myRS = myCon.OpenSchema(adSchemaTables)

For i = 1 To myRS.Fields.Count
  'フィールド名を書き出す
  Cells(1, i) = myRS.Fields(i - 1).Name
Next

'レコードセットの内容を転記
Range("A2").CopyFromRecordset myRS

myRS.Close: Set myRS = Nothing
myCon.Close: Set myCon = Nothing

End Sub

Set Recordsetオブジェクト = Connectionオブジェクト.OpenSchema(adSchemaTables)

実行結果

Excelブック内の、ADOで扱えるシート名や名前付き範囲の一覧を取得するには、ConnectionオブジェクトのOpenSchemaメソッドを使用する。
テーブルとして扱えるシート名や名前付き範囲の情報を取得するにはOpenSchemaメソッドの引数に「adSchemaTables」を指定して、実行した結果セットをRecordsetオブジェクトで受け取って利用する。

受け取った結果セットの内、テーブル名、つまりExcelブック内のシート名と名前付き範囲の一覧は、「TABLE_NAME」フィールドの値となる。