Excel（エクセル）のマクロ・ＶＢＡ・関数・操作などのメモ

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

　

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

　

実行結果

- データベース ADO, excel vba, Excelファイル, OLEDB, Select文, SQL, VBA中・上級, エクセル VBA, データベース

Tweet

Message コメントをキャンセル

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。

関連記事

: Recordset オブジェクト内のレコード数（ADO）RecordCount プロパティ

RecordCount プロパティ Recordset オブジェクトのレコード数 …

: データベース（Oracle）に接続する（ODBC を使用）

ODBC接続（ADO） ADOで、ODBC（Open Database Conn …

: インデックス、プロパティの設定（ADOX）

インデックスを作成する index オブジェクト【書式】＜CreateObj …

: フィールド名（ADO）Field オブジェクト

Field オブジェクト Recordset オブジェクト内の列（フィールド）を …

: クエリの作成・削除（ADOX）

ADOX を使用してクエリを作成・削除クエリを作成する手順【書式】＜参照設 …

: テーブルの作成・削除／フィールドの作成・削除（ADOX）

ADOX を使用してテーブル・フィールド（列）を作成・削除 Table オブジェ …

: レコードの追加（ADO）AddNew メソッド,Update メソッド

データベースにレコードを追加する AddNew メソッド新規レコードを追加し、 …

: レコードを並べ替える（ADO）Sort プロパティ

データベースのレコードを並べ替える Sort プロパティ【書式】 object …

: クエリにパラメータを渡して結果を取得する（ADO）Command オブジェクト・Execute メソッド

クエリにパラメータを渡して結果を取得する手順 Command オブジェクトの作成 …

: データベース（Oracle）に接続する（OLE DB を使用）

OLE DB プロバイダ OLE DBには、Oracle 提供の Oracle …

PREV: トランザクション（ADO）BeginTrans、CommitTrans、RollbackTrans
NEXT: CSVファイルに接続（ADO）