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

こんにちはJitanTechです。

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

今回はそのような方のために、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

解説

接続オブジェクト

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

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

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

Recordset オブジェクト (ADO）

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

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

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

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

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

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

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

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

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

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

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

コネクションを開く

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

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

SQL文でExcelシートを指定

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

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

SQL文の実行

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

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

実行結果

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

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

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

まとめ

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

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

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

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

qiita.com

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

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

atsumitm.iobb.net

ではまた。