VBA：【ADO】【取込み】ExcelファイルのAccessテーブルへのインポート・取込み「SQL文」
ここでは「ActiveX Data Objects(ADO)」と「SQL文」を使ったCSVの取込み方法について説明していきます。ここではADOを利用して、Excelを取り込んでいます。
※ADOを利用するには、VBEの参照設定で「Microsoft ActiveX Data Objects x.x Library」(「x.x」は「6.1」など)を有効にする必要があります。
◆Excel取込みのサンプル
以下ではExcelから外部のAccessに接続して、そのAccessDBのテーブルにExcelのデータを取り込んでいます。
コード内の(1)で囲った部分がExcelでの実行する際に必要なもので、Accessで実行したい場合は、(1)をコメントアウトして、(2)のコード部分(set部分)をコメントから外せばAccessで実行できます。
|
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 50 51 52 53 54 55 56 57 58 59 60 61 62 |
Sub AcessDB_Excel取込み() Dim xTmpPath As String Dim DBFile As String Dim SheetName As String Dim CN As ADODB.Connection Dim RS As ADODB.Recordset Dim xSQL As String 'フルパス。作成するAccessのファイル名まで書く' DBFile = "C:\Users\VBA\ADOX\サンプル.accdb" xTmpPath = "C:\Users\VBA\CVS取込み\サンプル1.xlsx" SheetName = "Sheet1" '//AccessDBに接続' '▽(1)Excelなど外部からの接続の場合------------------' 'Excelで実行し、外部ファイルのAccessに接続する必要がある場合は以下のようになります。' Set CN = New ADODB.Connection CN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile CN.Open '△(1)Excelなど外部からの接続の場合------------------' '▽(2)Accessのローカルテーブルの場合------------------' 'Accessで実行し、ローカルテーブルに接続する場合は以下のようになります。' ' Set CN = CurrentProject.Connection' '△(2)Accessのローカルテーブルの場合------------------' '既存のAccessDBのテーブルにデータが入っている場合はクリア' xSQL = "Delete * FROM [テーブル1]" Set RS = CN.Execute(xSQL) '以下のSQL文で取り込む' xSQL = "INSERT INTO [テーブル1](" xSQL = xSQL & "[番号]" xSQL = xSQL & " ,[名前]" xSQL = xSQL & " ,[科目]" xSQL = xSQL & " ,[点数]" xSQL = xSQL & ")" xSQL = xSQL & "SELECT" xSQL = xSQL & "[番号]" xSQL = xSQL & " ,[名前]" xSQL = xSQL & " ,[科目]" xSQL = xSQL & " ,[点数]" xSQL = xSQL & " FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE=" & xTmpPath & "].[" & SheetName & "$]" '「HDR＝YES」はExcelファイルの1行目をヘッダーとして扱うかどうかを指定する箇所で、「YES」の場合は1行目をフィールド名として扱います。' '「NO」の場合は「F1」から始まる列番号で読み込まれます。' 'その場合、上記のSQL文でもフィールド名ではなく、「F1」などの番号で対応します。' '-------------------------------' 'Excelの2行目にフィールド名が来ている場合は、以下のようにこのSQL文の場合は最終行にスタートするセル位置(A2)と最終列(D)を記載します。' 'xSQL = xSQL & " FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE=" & xTmpPath & "].[" & SheetName & "$A2:D]"' '-------------------------------' 'SQLの実行' Set RS = CN.Execute(xSQL) 'AccessDBの接続解除' CN.Close Set CN = Nothing Set RS = Nothing End Sub |
