スポンサードリンク

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

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

ワークシートの場合

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

select * from [シート名$]

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

名前定義範囲の場合

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

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

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

ListObjects(Excelのテーブル)の場合

シート名「Sheet1」のセル範囲に「個人情報」というテーブルを設定している場合は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種類の使い分けはコード中段あたりに記載しています。

まとめ

今回は実務で、あるシートの10行目より下を集計したいと思った時にどうすれば良いのかを解決するために調べました。

名前定義とテーブルを同一視していたため正解にたどり着くまで時間がかかってしまった。
以下のサイトでセル範囲を直接指定してるのを見つけれたことで何とかなりました。 ※しかし本当に直接テーブルを指定する方法はないのかな

https://support.microsoft.com/ja-jp/help/278973/excelado-demonstrates-how-to-use-ado-to-read-and-write-data-in-excel-w