データ検索 ADO 覚書

by

参照設定する方がよい。

  • Microsoft ActiveX Data Objects Recordset 6.0 Library
  • Microsoft ActiveX Data Objects 6.1 Library
Option Explicit

Public Sub adotest()
    Const adOpenKeyset = 1
    Const adLockReadOnly = 1
    Dim cn As Object
    Dim aRs As Object
    Dim query As String
    
    Set cn = CreateObject("ADODB.Connection")
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
    'レコードセット作成
    Set aRs = CreateObject("ADODB.Recordset")
    ' コネクションオープン、接続先は自分自身
    cn.Open ThisWorkbook.FullName
    query = " SELECT ID, NAME, VALUE " _
        & " FROM [Sheet1$] " _
        & " ORDER BY ID"
        
    aRs.Open query, cn, adOpenKeyset, adLockReadOnly
        
    If Not aRs Is Nothing Then
        If aRs.RecordCount > 0 Then
            Do Until aRs.EOF
                MsgBox (aRs("ID") & " :" & aRs("NAME") & " :" & aRs("VALUE"))
                aRs.MoveNext
            Loop
        End If
    End If

End Sub

ADOの参照を設定して、コネクションを設定し、レコードセットを作成。

コネクション
Microsoft.ACE.OLEDB.12.0 --> Office2007以降
Office2003 --> Microsoft.ACE.OLEDB.4.0

拡張プロパティ
cn.Properties("Extended Properties") = "Excel 12.0 --> Office2007以降
Office2003 --> cn.Properties("Extended Properties") = "Excel 8.0
HDR=Yes --> 1行目ヘッダー　HDR＝No --> ヘッダーなし
IMEX=1 --> 書き込み、 2 --> 読み込み、 3 --> 書き込みと読み込み

エクセル対象にする場合は、接続先は自分自身
　ThisWorkbook.FullName
From句はシート名 + $


参照設定をした方が速度も速いらしいが、なによりも入力支援機能が使えるなど開発しやすい。
でも、参照設定をすると、後日違うPCから実行したらエラーを吐くかもしれない。
上の例では、Objectで宣言して、CreateObjectにより、実行時バインディングしているので大丈夫。

参照設定を利用する事前バインディングでは、

 Dim cn As ADODB.Connection　　 　'ADOコネクションオブジェクト
 Dim aRs As ADODB.Recordset 　　　'ADOレコードセットオブジェクト

 Set cn = New ADODB.Connection      'ADOコネクションのインスタンス作成

 Set aRs = New ADODB.Recordset       'ADOレコードセットのインスタンス作成

他のBookにデータがある場合はConnectionStringにData Sourceを登録する。

Option Explicit

Public Sub test()
    Const adOpenKeyset = 1
    Const adLockReadOnly = 1
    Dim cn As Object
    Dim aRs As Object
    Dim query As String
    Dim dataFile As String               '抽出対象のデータが登録されたExcelファイル
    
     '抽出対象のデータが登録されたExcelファイル
    dataFile = ActiveWorkbook.Path & "\testADO_Data.xlsx"
    
    Set cn = CreateObject("ADODB.Connection")
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
    'レコードセット作成
    Set aRs = CreateObject("ADODB.Recordset")
    
    ' コネクションオープン、接続先は他のBook
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
        .ConnectionString = "Data Source = " & dataFile & _
        ";Extended Properties =Excel 12.0;"
        .Open
    End With
    query = " SELECT ID, NAME, VALUE " _
        & " FROM [Sheet1$] " _
        & " ORDER BY ID"
        
    aRs.Open query, cn, adOpenKeyset, adLockReadOnly
        
    If Not aRs Is Nothing Then
        If aRs.RecordCount > 0 Then
            Do Until aRs.EOF
                MsgBox (aRs("ID") & " :" & aRs("NAME") & " :" & aRs("VALUE"))
                aRs.MoveNext
            Loop
        End If
    End If

End Sub