Excelファイルに接続(ADO)
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 ・・・ 3つのプロパティ値を指定します。各プロパティ値は「;(セミコロン)」で区切ります。全体を「”」で括ります。
(例)Extended Properties = “Excel 12.0;HDR=Yes;”
Extended Properties に指定する各プロパティ値について
・Excel 12.0 の部分は、データベースの種類を表しています。Excel2007以降で作成したブックに接続する場合は、Excel 12.0、Excel2002/2003で作成したブックに接続する場合は、Excel 8.0 を指定します。
・HDR は、シートの1行目をフィールド名として扱うかどうかを指定します。No を指定した場合、フィールド名は、F1、F2、F3 のように F[列番号] で表示されます。
Excelのシート、セル範囲を、テーブルとして扱います。
ワークシート全体:Select * from [Sheet1$]
名前なしセル範囲:Select * from [Sheet1$A2:E10]
名前付きセル範囲:Select * from 範囲名
Excelファイルへ接続する例
サンプルVBAソース
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)Command オブジェクト・Execute メソッド
クエリにパラメータを渡して結果を取得する手順 Command オブジェクトの作成 …
-
データベース(Oracle)に接続する(OLE DB を使用)
OLE DB プロバイダ OLE DBには、Oracle 提供の Oracle …
-
テーブル名、クエリ名の取得(ADOX)
テーブル名の取得 Table.Type プロパティ 【書式】 <取得> obje …
-
フィールド名(ADO)Field オブジェクト
Field オブジェクト Recordset オブジェクト内の列(フィールド)を …
-
フィールド名(ADO)Fields コレクション
Fields コレクション Recordset オブジェクト内の1つ以上の列(フ …
-
Recordset オブジェクト内のレコード数(ADO)RecordCount プロパティ
RecordCount プロパティ Recordset オブジェクトのレコード数 …
-
データベースを作成する(ADOX)
ADOX を使用してデータベース(Access)を作成 Catalog オブジェ …
-
CSVファイルに接続(ADO)
ADOでCSVファイルに接続 ADO を使用してCSVファイルを読み込みます。C …
-
レコードの削除(ADO)Delete メソッド
データベースのレコードを削除する Delete メソッド カレントレコードを削除 …
-
レコードの追加(ADO)AddNew メソッド,Update メソッド
データベースにレコードを追加する AddNew メソッド 新規レコードを追加し、 …