ADOを使ってExcelをテーブルとして読み込む方法について

今回のエントリーはExcelのシートに作成した表のデータをADOを使ってテーブルとして読み込む方法をアップしたいと思います。
関連するエントリー「Excelのシートを複数条件で検索するVBAサンプルコード（ADO）」も参考下さい。

今回のサンプルの表はかなりシンプルなものですが・・・。動作イメージは以下の通りです。

ADOを使う為、まずはVBE(Visual Basic Editor)の参照設定でMicrosoft ActiveX Data Objects 2.X Libraryにチェックを入れましょう。
※参照設定の方法はこちらのページを参照

1.ボタンをクリックした際の読み込み用のコード

Private Sub cmdDisp_Click()
    Dim adoCON      As New ADODB.Connection
    Dim adoRS       As New ADODB.Recordset
    Dim strSQL      As String
    Dim odbdDB      As Variant

    'リストボックスをクリア
    lstName.Clear

    'データベースのパスを取得（ExcelブックをDBとする）
    odbdDB = ActiveWorkbook.Path & "\sample_140129.xlsm"
    
    'データベースに接続する
    Set adoCON = New ADODB.Connection

    With adoCON
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Extended Properties") = "Excel 8.0"
        .Open odbdDB
    End With
    
    'カーソルをクライアント側に設定
    adoRS.CursorLocation = adUseClient
    
    'Sheet1をテーブルとしてSQLを設定
    strSQL = "SELECT * FROM [Sheet1$];"
    
    'レコードセットを開く
    adoRS.Open strSQL, adoCON, adOpenDynamic
    
    'テーブルを読み込む
    Do Until adoRS.EOF
        '検索対象の住所（A列）をリストボックスに追加
        lstName.AddItem adoRS.Fields(0).Value
        adoRS.MoveNext
    Loop
 
    'クローズ処理
    adoRS.Close
    Set adoRS = Nothing
    adoCON.Close
    Set adoCON = Nothing
End Sub

2.VBAコードの解説

ADOを使用してExcelに接続する

11～20行目でExcelのワークブックをデーターベースのテーブルとしてADOで接続しています。
具体的には11行目でデータベースとして利用するExcelのパスを設定し

'カレントディレクトリのデータベースパスを取得
odbdDB = ActiveWorkbook.Path & "\sample_140129.xlsm"

16～20行目でMicrosoft Jet OLE DB Provider (以下 “Jet プロバイダ”) を利用して接続します。

'データベースに接続する
Set adoCON = New ADODB.Connection

With adoCON
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties") = "Excel 8.0"
    .Open odbdDB
End With

Excelの表をテーブルとして開きます

次にテーブルを開く処理について説明します。
Excleのシート（シート名はSheet1）に作成した表をテーブルと見なしてSQLを作成し（26行目）レコードセットを開きます（29行目）。

'SQLを設定
strSQL = "SELECT * FROM [Sheet1$];"

'レコードセットを開く
adoRS.Open strSQL, adoCON, adOpenDynamic

Excelの表を繰り返し読み込んでフォームのリストボックスに表示

リストボックスにテーブルの値を繰り返し読み込みます（32～36行）
テーブルの値はFieldsコレクションを利用して最初のフィールド　Fields(0)　を参照しています。

'テーブルを読み込む
Do Until adoRS.EOF
    '検索対象の住所（A列）をリストボックス（lstName）に追加
    lstName.AddItem adoRS.Fields(0).Value
    adoRS.MoveNext
Loop

以上、今回はExcelをテーブルとしてADOを使って利用する為のVBAコードでした。