VBAでSQLを使用する際にListObjects(テーブル)をFrom句に指定する方法

ADODBを利用することでExcelのデータをSQLで操作することが出来るようになります。
SQL文のFrom句でワークシート名を指定する方法は知っていたのだが、名前定義の範囲やテーブルを指定する方法を知りたかったので調べてみた。

ワークシートの場合

まずは基本のワークシート名を指定する場合はSQL文を以下のようにする

select * from [シート名$]

[ ]の中にシート名$と記載すればOK。

名前定義範囲の場合

セル範囲に「なんちゃって個人情報」という名前定義をしている場合はSQL文を以下のようにする。

select * from なんちゃって個人情報

名前定義の場合は特に何かを付け足す必要はなく、名前定義名を記載すればOK

ListObjects（Excelのテーブル）の場合

シート名「Sheet１」のセル範囲に「個人情報」というテーブルを設定している場合はSQL文を以下のようにする。

select * from [worksheets("Sheet1").listobjects("個人情報").range.address(false,false)]

名前定義のときのようにテーブル名を指定すれば良いという訳ではなく、ちょっと遠回りの設定が必要です。 ListObjectsからRangeオブジェクトを取得してそのアドレスを指定しています。

www.relief.jp

Addressプロパティは引数を指定しない場合絶対参照（$A$1）でアドレスを返します。今回の用途では絶対参照にすると実行時エラーでマクロが正常に作動しません。

Addressプロパティの第1引数・第2引数にFalseを指定すると相対参照（A1）でアドレスが返ってきます。

コードサンプル

Option Explicit

Sub ListObjectToTable()
     Dim myCon As ADODB.Connection
     Dim myRS As ADODB.Recordset
     
     Set myCon = New ADODB.Connection
     With myCon
          .Provider = "Microsoft.ACE.OLEDB.12.0"
          .ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
               ";Extended Properties =Excel 12.0;"
          .Open
     End With
     Set myRS = New ADODB.Recordset
     
'ワークシートの場合
     myRS.Open "select * from [Sheet1$]", myCon, adOpenStatic
'名前定義の場合
'     myRS.Open "select * from なんちゃって個人情報", myCon, adOpenStatic
'ListObjects（テーブル名）の場合
'     myRS.Open "select * from [" & Worksheets("Sheet1").ListObjects("個人情報").Range.Address(False, False) & "]", myCon, adOpenStatic

     Dim myVar As Variant
     myVar = myRS.GetRows
     myVar = WorksheetFunction.Transpose(myVar)
     Worksheets("Paste").Cells.Clear
     Worksheets("Paste").Range("A1").Resize(UBound(myVar), UBound(myVar, 2)).Value = myVar
     
     Set myRS = Nothing
     Set myCon = Nothing
End Sub

事前にツール→参照設定から Microsoft ActiveX Data Objects XX(数字) Library を参照するようにチェックを付けておいてください。
上記のコードはおそらくExcel2007以降で正常に動作すると思います。
Excel2003までの場合はExtended Prperties = Excel12.0"をExtended Prperties = Excel8.0にすれば動作するはず。

上記で紹介した3種類の使い分けはコード中段あたりに記載しています。