【Excel VBA】ExcelシートをデータベースとみなしてSELECT文を実行する方法

こんにちはJitanTechです。

みなさんはExcelをデータベースとみなして、結果を取得したいとおもったことはありませんか。

今回はそのような方のために、Excelに対してSQL文を実行する方法をご紹介します。

この記事で学べること

ExcelにSQL文を実行する方法。

ではいってみましょう。

サンプルプログラム
解説
実行結果
まとめ

サンプルプログラム

次のサンプルではExcelシートにSELECT文を流して結果を取得します。

Sub Sample()
    
    ' コネクション、レコードセット変数
    Dim cn As Object
    Dim rs As Object
    
    ' コネクション、レコードセットを生成
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    ' コネクション接続文字列
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES"
    
    ' コネクションオープン
    cn.Open ThisWorkbook.FullName
 
    ' SQL文 ここでシート名を指定する[Excelシート名$]の形式です。
    Dim strSQL As String
    strSQL = ""
    strSQL = strSQL & " SELECT *"
    strSQL = strSQL & " FROM [Excelシート名$]"
    strSQL = strSQL & " ORDER BY 商品コード "
    
    ' レコードセットにSQLの結果を入れる
    rs.Open strSQL, cn
 
    ' 結果出力
    Cells(1, 1).CopyFromRecordset rs
    
    ' 変数の開放
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
End Sub

Sub Sample()

' コネクション、レコードセット変数

Dim cn As Object

Dim rs As Object

' コネクション、レコードセットを生成

Set cn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

' コネクション接続文字列

cn.Provider = "Microsoft.ACE.OLEDB.12.0"

cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES"

' コネクションオープン

cn.Open ThisWorkbook.FullName

' SQL文ここでシート名を指定する[Excelシート名$]の形式です。

Dim strSQL As String

strSQL = ""

strSQL = strSQL & " SELECT *"

strSQL = strSQL & " FROM [Excelシート名$]"

strSQL = strSQL & " ORDER BY 商品コード "

' レコードセットにSQLの結果を入れる

rs.Open strSQL, cn

' 結果出力

Cells(1, 1).CopyFromRecordset rs

' 変数の開放

rs.Close

Set rs = Nothing

cn.Close

Set cn = Nothing

End Sub

解説

接続オブジェクト

ExcelにSQL文を投げるためには接続オブジェクトを生成する必要があります。

接続オブジェクト（ADO）の生成

Set cn = CreateObject(“ADODB.Connection”)

このオブジェクトを通してExcelブックにアクセスします。

※ADO(ActiveX Data Objects)とは ADOとは、Microsoftが提唱しているデータアクセス技術のことです。

Recordset オブジェクト (ADO）

レコードセットのOPENメソッドにSQL文を渡すため、レコードセットを生成します。

Recordset オブジェクト (ADO）の生成

Set rs = CreateObject(“ADODB.Recordset”)

このレコードセットオブジェクトのOPENメソッドにSQL文を渡すことでクエリが実行できます。

コネクションのプロバイダ

コネクションのプロバイダはExcelブックとADOをつなぐ役割をもちます。

Provider プロパティ (ADO)

cn.Provider = “Microsoft.ACE.OLEDB.12.0”

プロバイダープロパティにプロバイダーを文字列で指定します。以下のように使い分けできます。

指定文字列	内容
Microsoft.Jet.OLEDB.4.0	Office2003以前の場合
Microsoft.ACE.OLEDB.12.0	Office2007以降の場合

コネクションのプロパティ

ExcelのシートやCSVをデータベースにする際に指定します。

拡張プロパティの指定

cn.Properties(“Extended Properties”) = “Excel 12.0;HDR=YES”

以下のように使い分けできます。

指定文字列	内容
Excel 8.0	Office2003以前の場合
Excel 12.0	Office2007以降の場合
Text	csvファイルの場合

指定文字列	内容
HDR=YES	1行目をヘッダーとみなす場合
HDR=NO	1行目をヘッダーとみなさない場合

コネクションを開く

データベースとして開くファイルを指定します。

拡張プロパティの指定

cn.Open ThisWorkbook.FullName

ここではThisWorkbook.FullNameとすることで、自身のExcelブックを指定しています。

SQL文でExcelシートを指定

Excelシートをデータベースのテーブルとして指定するには以下のようにします。

Excelシートの指定

strSQL = strSQL & ” SELECT *”
strSQL = strSQL & ” FROM [Excelシート名$]“
strSQL = strSQL & ” ORDER BY 商品コード “

[Excelシート名$]というように、Excelシート名の後ろに $ をつけることがポイントです。そして[]で囲むことも必要です。

SQL文の実行

レコードセットオブジェクトのOPENメソッドでSQL文を実行します。

Excelシートの指定

rs.Open strSQL, cn

第一引数にSQL文、第二引数にコネクションオブジェクトを渡して実行します。

実行結果

以下のようなExcelシートに対してSELECT文を発行します。

以下のような結果を得ることができました。

SELECT文で結果が取得されました。

まとめ

この記事ではExcelシートをデータベースのテーブルのように扱い、SQL文を発行する方法をご紹介しました。

今回のポイント

・ADO(ActiveX Data Objects)を使用する。

・Excelシート名の後ろに$をつけて[]で囲む。

以下は参考にした記事です。

【VBA】ADOを使用してExcel表をDB操作する方法のまとめ（範囲指定、書き込みなど） - Qiita

はじめにこの記事は、ADO（ActiveX Data Objects）を使用して、ExcelファイルをSQL文で操作する方法のまとめです。大半はよくある内容ですが、ワークシートの読み込みから、D…

【Excel】ADOでAccess･Excel･CSVへ接続・操作_JET･ACEプロバイダ編

ADOでプロバイダとしてACE･Jetを使ったExcelからAccess･Excel･CSVへのデータベース接続とSQL操作を説明。接続時のパラメータでデータ操作可否が変わる為、発生するプロパティ値を示して詳細説明。

ではまた。