AccessVBA_ADOの利用_【Recordset】Excelファイルに接続

Excelファイルに接続しその内容（Recordset）を取得

今回の記述に関する前情報

何を行うか

Excelファイル（外部ファイル）に接続し、SQLステートメントで指定したレコードセットを取得
CSVのときとほとんど同じだけれど、書式が若干異なるので要注意

利用時の書式

以下のうちどちらの書き方でもOK

・ConnectionオブジェクトのOpenメソッドの引数にそれ用の接続文字列を設定する場合
Connectionオブジェクト変数.Open "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = Excelファイルのフルパス; Extended Properties = 'Excel 12.0;HDR=NO'"
Set Recordsetオブジェクト変数 = Connectionオブジェクト変数.Execute("SELECT * FROM [シート名 $]")

・ConnectionオブジェクトのConnectionStringプロパティにそれ用の接続文字列を代入してからOpenする場合
Connectionオブジェクト変数.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = Excelファイルのフルパス; Extended Properties = 'Excel 12.0;HDR=NO'"
Connectionオブジェクト変数.Open
Set Recordsetオブジェクト変数 = Connectionオブジェクト変数.Execute("SELECT * FROM [シート名 $]")

記述例における利用パーツ

・ステートメント
　　→Do Loop
・プロパティ
　　→Field
・メソッド
　　→MoveNext / Open / Close

注意点

Excel 12.0;HDR=NO'　→　1行目はデータとして認識
書式　SELECE FROM [シート名$] WHERE フィールド名 = 値
Excel 12.0;HDR=YES'　→　1行目はフィールド名として認識
書式　SELECE FROM [シート名$] WHERE [シート名$].列番号 = 値
※NOに設定している場合、WHERE句でシート名を指定することは出来ない
※列番号は「F1」「F2」のように、あたまにFをつけて記述する

【実践】Excelファイルに接続する記述例

カレントプロジェクトのデータベースに接続する場合の記述
カレントディレクトリにあるExcelファイル「yubin_data.xlsx」に接続してRecordsetを取得し
左から4つのフィールドをイミディエイトウィンドウに表示

▼取得対象ファイル「yubin_data.xlsx」▼


▼記述例▼

Sub read_excel()

Dim CNT As New ADODB.connection
Dim RST As ADODB.Recordset
Dim ExcelPath As String

ExcelPath = CurrentProject.Path & "\"
CNT.Open "Provider = Microsoft.ACE.OLEDB.12.0; " & _
                     "Data Source = " & ExcelPath & "yubin_data.xlsx;" & _
                     "Extended Properties = 'Excel 12.0;HDR=YES'"

Set RST = CNT.Execute("SELECT * FROM [yubin_data$] WHERE prefecture = 'ほっかいDo'")
Do Until RST.EOF
    Debug.Print RST.Fields("postcode"), RST.Fields("prefecture"), RST.Fields("city"), RST.Fields("others")
    RST.MoveNext
Loop

RST.Close: CNT.Close
Set RST = Nothing: Set CNT = Nothing

End Sub

▼実行結果▼

基本的に上述の書き方で問題ないが、以下の記述をしても同じ結果を得ることができる

Sub test_excel_2()

Dim CNT As New ADODB.connection
Dim RST As ADODB.Recordset
Dim ExcelPath As String

ExcelPath = CurrentProject.Path & "\"
CNT.Provider = "Microsoft.ACE.OLEDB.12.0"
CNT.Properties("extended Properties") = "Excel 12.0;HDR=YES"
CNT.ConnectionString = ExcelPath & "yubin_data.xlsx"
CNT.Open

Set RST = CNT.Execute("SELECT * FROM [yubin_data$] WHERE prefecture = 'ほっかいDo'")
Do Until RST.EOF
    Debug.Print RST.Fields("postcode"), RST.Fields("prefecture"), RST.Fields("city"), RST.Fields("others")
    RST.MoveNext
Loop

RST.Close: CNT.Close
Set RST = Nothing: Set CNT = Nothing

End Sub

フィールド名を取得したい場合には、以下の記事を参照するとよいかも
▼Recordsetオブジェクトの確認方法を復習▼
Recordsetのフィールド名・列名を取得する:ADOの使い方