小さい会社の1人SEの開発記というか奮戦記

まずは一番簡単な内容です。エクセルのVBAからアクセスの Northwind データーベースに接続し、テーブルのデータ全てをエクセルのワークシートに取り込みます。

条件整理

Accessファイル	D:¥db¥NorthWind.accdb
インポート元	テーブル「Employees」
インポート先	Excelシート名「Sheet1」セルA5
処理	全件コピー

ExcelからAccessに接続するには事前準備が必要です。事前に「ExcelからVBAでデーターベースを使う準備」を参照しておいて下さい。

VBAでのデータ取り込み手順

一連の手順をまとめると以下となります。

1. 変数の宣言
2. データベースに接続
3. 取得したデータをワークシートに挿入する
4. データベースの接続を解除する

変数の宣言

Dim strfn As String ‘アクセスファイル名
Dim adocn As Object ‘ADOコネクションオブジェクト
Dim adors As Object ‘ADOレコードセットオブジェクト

参照 →「MSDN Connection オブジェクト」
参照 →「MSDN Recordset オブジェクト」

データベース接続

‘　　アクセスファイル名
strfn = “D:¥db¥NorthWind.accdb”

‘　　ADOコネクションオブジェクトを作成
Set adocn = CreateObject(“ADODB.Connection”) 

‘　　Accessファイルに接続(アクセスに接続する場合の決まり文句だと思って下さい)
adocn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & strfn & “;”

‘　　ADOレコードセットオブジェクトを作成
Set adors = CreateObject(“ADODB.Recordset”)

データをワークシートに挿入

‘　　対象テーブルであるEmployeesをRecordSetへセット
adors.Open “Employees”, adocn 

‘　　RecordSetへセットしたデータをセルA5に挿入
Range(“A5”).CopyFromRecordset adors

参照 →「MSDN Open メソッド」

データベース接続解除

‘　　レコードセットのクローズ
adors.Close

‘　　コネクションのクローズ
adocn.Close

‘　　オブジェクトの破棄
Set adors = Nothing
Set adocn = Nothing

まとめ

[ ボタン 1 ]をクリックするとセルA5にデータが挿入されます。

作成したVBAは以下の通りです。

Sub ボタン1_Click()

'   変数宣言
Dim strfn As String '　　アクセスファイル名
Dim adocn As Object '　　ADOコネクションオブジェクト
Dim adors As Object '　　ADOレコードセットオブジェクト

'   アクセスファイル名
strfn = "D:\db\NorthWind.accdb"
 
'　　データベース接続 
Set adocn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adocn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strfn & ";" 'Accessファイルに接続
Set adors = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
 
adors.Open "Employees", adocn '対象テーブルをレコードセットへ
Range("A5").CopyFromRecordset adors 'レコードセットのデータをセルA5に挿入

'　　データベース接続解除 
adors.Close 'レコードセットのクローズ
adocn.Close 'コネクションのクローズ
 
Set adors = Nothing  'オブジェクトの破棄
Set adocn = Nothing
     
End Sub

おそらくVBAではデータベースからデータを取り込む最小プログラムだと思います。全ての基本になりますのでよく覚えておきましょう。

関連記事 →「ExcelからVBAでデーターベースを使う準備」