ExcelとAccessを連携しよう(1)

みなさん、こんにちはKITAです。社内のデータベースにAccessを使っていらっしゃる方は多いと思います。ですが、アクセスにはエクセルのような計算機能がないので、エクセルでデータ処理してからアクセスにデータを入力している方は多いのではないでしょうか?エクセルのセルをコピーして、アクセスのフォームにペースト、というなかなかハードな作業をこなしていらっしゃるかたもいらっしゃいます。そこで、業務の自動化にもつながる、エクセルとアクセスの連携方法をご紹介いたします。これができるようになると、エクセルでデータの集計処理、そしてエクセルからアクセスに直接データを入力することができます。

ADOを使おう!

今回はADO(ActiveX Data object)を使います。Accessだけでなく、Oracle、MySQLなどの様々なデータベースに対して共通の手法で操作が出来る仕組みです。ExcelからADOを使用するためには、まず参照設定を行います。


事前準備としてVBEの「ツール」メニュー → 「参照設定」をクリックし、「参照設定」ダイアログボックスを表示し、「Microsoft ActiveX Data Objects X.X Library」にチェックを入れます(X.Xはバージョン)。

 

Excel上でAccessデータベースに接続してレコードを読み込む。

ExcelからAccessデータベースに接続して、レコードを読み込み、表示しましょう。

まず、Access上にテーブルを作成します。ID,品名、型番、単価のシンプルなレコードを作成します。テーブル名はt_itemとします。テーブルデザインや入力内容は以下の通りです。

 

アクセスのテーブル

次に、ExcelからAccessのテーブルをADOで読み込む為の設定を行います。Excelを起動し、VBE(Visual Basic Editor)を[Alt]+[F11]で起動し、ツール(T) → 参照設定(R) をクリック。

参照設定のウィンドウが開いたらMicrosoft ActiveX Data Objects 2.X Libraryにチェックを入れます。

 

最後に、テーブルをADOで読み込む為のVBAコードを作成します。

 

 

VBAソースの説明

1.変数の設定

3-8行目でADOで使用する変数を宣言します。

  1. Excelブックと同じフォルダ内のAccessのtest.accdbのフルパスを取得する。
    11行目でActiveWorkbook.Pathを使いExcelのフォルダを取得します。

 

2.データベース接続

14~16行目でAccessデータベースに接続します。
今回使用するアクセスのバージョンは2007以降なので、providerにはMicrosoft.ACE.OLEDB.12.0を指定して下さい。

 

 

3.SQLを実行

19行目でDBに接続するSQLを実行します。アクセスのテーブル名を記述します。

 

4.レコードセットを開く
22行目のOpenメソッドでレコードセットを開きます。

 

5.アクティブシート名を取得

25行目で現在アクティブになっているワークシート名を取得します。

 

6.スタート行をセット

28行目でエクセルシートに書き込みをはじめ行数をセットします。

 

7.テーブル内のデータを全てExcelに出力
31~41行目でDo Until Loopを使い、レコードをExcelに繰り返して出力します。

 

8.データベースをクローズ
44-47行でCloseメソッドを使いレコードセットとADOコネクションオブジェクトを閉じます。それぞれの変数にNothingをセットしてクリアします。

 

 

マクロの実行

Access_read()マクロを実行しましょう。

エクセルにアクセスのデータが転記されました。

 

エクセル&アクセステンプレート差し上げます。

今回作成したエクセルとアクセスのサンプルを無料で差し上げます。

お問い合わせから、コメント欄に「ExcelとAccessを連携してみよう(1)テンプレ希望」と記入して送信ください。

 

まとめ

以上、今回はExcelからAccessにADOを使って接続する方法でした。次回は、エクセルからアクセスに書き込む方法をご紹介します。

 

エクセルマクロVBA作成に困ったら

VBAのコードがわからず検索してもなかなかいいコードが見つからない、そんんなお悩みはございませんか?開発委託・コンサルティング・ワンポイントサポートいたします。お気軽にどうぞ。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です