【ExcelVBA 外部アプリケーション連携】（Access連携）Accessテーブルデータを取り込むにはどうすればいいの？教えて！

　　　　　　　 f:id:m_kbou:20200514110907p:plain

AccessテーブルデータをExcelシートへ取り込む方法について説明します。ExcelとAccessとの連携によるデータのやり取りでは基本中の基本操作となります。是非一連の流れを理解して活用してみて下さい。

【目次】

構文
使い方
おわりに

構文

記述方法は、以下の通りとなります。

=======================================================================

Set［参照データベース］= OpenDatabase（”［データベースの登録パス］”）

Set［参照テーブル］=［参照データベース］.OpenRecordset（”［SQLのSELECT文によるテーブルデータ抽出方法］”）

［参照テーブル］.MoveFirst
Do Until (［参照テーブル］.EOF)
　［取り込むシート名及びセル］ = ［参照テーブル］![項目名]
［参照テーブル］.MoveNext
Loop

［参照テーブル］.Close
Set［参照テーブル］= Nothing

［参照データベース］.Close
Set［参照データベース］= Nothing

=======================================================================

[説明]：

※Accessテーブルデータを取り込むには、前提条件としてAccessデータベースへの接続/切断及びテーブルへの接続/切断が必要となります。各々の記述方法については、今回説明するテーブルデータ取り込み構文の前後に小文字で記述しておきます。参考にして下さい。

［参照テーブル］.MoveFirstでは、テーブル中に登録されている最初のデータに移動します。Do Until (［参照テーブル］.EOF)～Loopでは、テーブル中のデータが最後になるまで繰り返し処理を行います。この繰り返し処理を行う中で、［参照テーブル］![項目名]のテーブルデータを［取り込むシート名及びセル］で指定したExcelシートのセルにセットします。また、［参照テーブル］.MoveNextでは、テーブル中の次のデータに移動させます。内容を纏めると、「［参照テーブル］に登録されているデータを最初から最後まで読み込み、［取り込むシート名及びセル］で指定したExcelシートの指定したセルに取り込んで下さい。」との意味になります。

[記述例]：

r.MoveFirst
Do Until (r.EOF)
　　Sheets("従業員").Range("A" & 行) = r![氏名]
行 = 行 + 1
r.MoveNext
Loop

使い方

使い方について、具体的に説明していきます。

[記述例]：

※以下の記述例は、VBE（VBA記述画面）に記述しないと実行ができません。VBEの開き方については、VBEの開き方を参考にして下さい。また、Accessデータベースとの接続には事前設定が必要となります。事前準備はAccessとの連携をするための事前準備を参考にして下さい。

-------------------------------------------------

Sub サンプル()

    '↓処理①
    Dim db As Database
    Dim r As Recordset
    Dim 行 As Integer
'↓処理②
    Set db = OpenDatabase("C:\テスト\従業員.accdb")
'↓処理③
    Set r = db.OpenRecordset("select * from T_従業員")
'↓処理④
    行 = 1
    r.MoveFirst
    Do Until (r.EOF)
        Sheets("従業員").Range("A" & 行) = r![氏名]
        Sheets("従業員").Range("B" & 行) = r![番号]
        Sheets("従業員").Range("C" & 行) = r![部署]
        Sheets("従業員").Range("D" & 行) = r![性別]
        Sheets("従業員").Range("E" & 行) = r![年齢]
        行 = 行 + 1
        r.MoveNext
    Loop
'↓処理⑤
    r.Close
    Set r = Nothing
'↓処理⑥
    db.Close
    Set db = Nothing

End Sub

-------------------------------------------------

処理の流れは以下の通りとなります。

［処理①］：変数の定義
Dimによる変数の定義となります。

［処理②］：データベースへの接続
データベースをOPENし［処理①］で定義した変数：dbにデータベースの参照を代入します。

［処理③］：テーブルへの接続
テーブルをOPENし［処理①］で定義した変数：rにテーブルの参照を代入します。

［処理④］：テーブルデータの取り込み
最初に［処理①］で定義した変数：行に1をセットします。
次にMoveFirstでテーブル中に登録されている最初のデータに移動します。
最後にDo～Loop文を使用してテーブルデータの最後（EOF）まで繰り返し処理を行います。繰り返し処理を行う中で、テーブルの各項目データ（氏名・番号・部署・性別・年齢）をExcelシートの指定セル（A列～E列セル）にセットします。セット後には変数：行に1を足し込み（行 = 行 + 1）、更にMoveNextでテーブル中の次のデータに移動させます。

［処理⑤］：テーブルの切断
テーブルの切断を行います。

［処理⑥］：データベースの切断
最後にデータベースの切断を行います。

[実行例]：

（実行前）

※C:\テストにAccessデータベース（従業員.accdb）が登録されています。

f:id:m_kbou:20200511165951p:plain