Excelファイルに接続（ADO）

2017/09/21

ADOでExcelファイルに接続

ADO を使用してExcelファイルを読み込みます。Excelファイルをデータベースとみなし、SQL文を使うことが出来ます。

【接続文字列】

＜ConnectionString（ADO）に設定する文字列＞
Provider = OLEDBProvider
Data Source = DBFileName;
Extended Properties = EXProperties;

引数・戻り値

DBFileName ・・・対象となるExcelファイルのパスとファイル名を指定します。）
OLEDBProvider ・・・ Excel2007以降で作成したブックに接続する場合は、Microsoft.ACE.OLEDB.12.0;、Excel2002/2003で作成したブックに接続する場合は、Microsoft.Jet.OLEDB.4.0; を指定します。
EXProperties ・・・３つのプロパティ値を指定します。各プロパティ値は「;（セミコロン）」で区切ります。全体を「”」で括ります。
（例）Extended Properties = “Excel 12.0;HDR=Yes;”

Extended Properties に指定する各プロパティ値について
・Excel 12.0 の部分は、データベースの種類を表しています。Excel2007以降で作成したブックに接続する場合は、Excel 12.0、Excel2002/2003で作成したブックに接続する場合は、Excel 8.0 を指定します。
・HDR は、シートの１行目をフィールド名として扱うかどうかを指定します。No を指定した場合、フィールド名は、F1、F2、F3 のように F[列番号] で表示されます。

Excelのシート、セル範囲を、テーブルとして扱います。
ワークシート全体：Select * from [Sheet1$]
名前なしセル範囲：Select * from [Sheet1$A2:E10]
名前付きセル範囲：Select * from 範囲名

Excelファイルへ接続する例

サンプルVBAソース

　
Sub Sample_ADO_Excel()

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim EXProperties As String
    Dim constr As String
    Dim DBFile As String
    Dim strSQL As String
    Dim i As Long
    Dim j As Long
    
    DBFile = ActiveWorkbook.Path & "\mydb1.xlsx"
    EXProperties = """Excel 12.0;HDR=No;"""
    
    constr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & DBFile & ";Extended Properties=" & EXProperties
    
    cn.ConnectionString = constr
    cn.Open
    
    strSQL = "Select * from [mydb1$];"
    rs.Source = strSQL
    rs.ActiveConnection = cn
    rs.Open
    
    With Worksheets("excel")
        
        .Cells.Clear
        i = 1
        Do Until rs.EOF
            For j = 0 To rs.Fields.Count - 1
                If i = 1 Then .Cells(i, j + 1) = rs.Fields(j).Name
                .Cells(i + 1, j + 1) = rs(j).Value
            Next j
            rs.MoveNext
            i = i + 1
        Loop
    
    End With
    
    rs.Close
    Set rs = Nothing
    
    cn.Close
    Set cn = Nothing
    
End Sub