教えて!ExcelVBA!

ExcelVBAの基礎知識・書き方について紹介します。

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

       f:id:m_kbou:20200514110907p:plain

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

 

【目次】

 

構文

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

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

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

※従業員.accdbデータベースの中には、T_従業員テーブルが登録されています。

f:id:m_kbou:20200514110932p:plain

※T_従業員テーブルの中には、以下のデータが登録されています。

f:id:m_kbou:20200514110956p:plain

※取り込む先のExcelシートには以下の様な<実行>ボタンが登録されており、ボタンには上記のプログラムが登録されています。

f:id:m_kbou:20200514111012p:plain

(実行後)

※指定のExcelシート及びセルにAccessテーブルに登録されているデータの取り込みができました。

f:id:m_kbou:20200514111028p:plain

Accessテーブルデータを取り込む方法についての説明は以上です。

 

おわりに

今回はAccessテーブルデータを取り込む方法について説明しました。テーブルデータを取り込むには、Do~Loop文を用いて指定したテーブルの最初のデータから最後のデータまで読み込みを行いながら指定シートの指定セルにセットする流れとなります。是非覚えておいて下さい。