【VBA】テーブル（Access）のデータをワークシートに転記する

こんにちは、Ryoです。
データベースとして利用することも多いMicrosoft Accessですが、そのテーブルデータをVBAのCopyFromRecordsetメソッド利用することでExcelのSheetにコピーすることができます。今回はその内容について書いていきます。

1.サンプル概要
2.サンプルコード
3.まとめ

1.サンプル概要

今回使用するサンプルとしてアクセスで以下のような簡単なテーブルを準備しています。データベースのファイル名は「SampleDatabase1.accdb」になります。

VBAを使用してデータベースに接続し、このテーブルのフィールド名やデータを取得してワークシートに貼付けます。

データを転記後に総データ数を確認する上でメッセージボックスで表示させています。

2.サンプルコード

Sub Sample1()
    Dim conect As Object, recs As Object
    Dim i As Long
    
    '※外部データベース（ここではMicrosoftAccess）を
    '　操作するにはActive Data Objects(ADO）を使用する
    
    '接続の確立用：Connectionオブジェクトを作成
    Set conect = CreateObject("ADODB.Connection")
    'Accessレコード取得用：Recordsetオブジェクトを作成
    Set recs = CreateObject("ADODB.Recordset")
    
    'Accessの「SampleDatabase1.accdb」と接続する
    conect.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & ThisWorkbook.Path & "\SampleDatabase1.accdb"
    
    '※※参考※※
    '　変数Providerに指定する外部データベースのプロバイダー名
    ' 　 Access2016/2013/2010/2007 : Microsoft.ACE.OLEDB.12.0
    '　  Access2003/2002　:　Microsoft.Jet.OLEDB.4.0
    '　  SQLServer　:　SQLOLEDB.1
    '　  Oracle : MSDAORA
    '　  DB2　:　IBMDADB2
    '　  ODBC接続　:　MSDASQL.1
    '※※※※※※
       
    '「顧客リスト」テーブルのデータを取得
    '最後にテーブルデータ数を取得するのでCursorTypeに
    'キーセットカーソル:1、または静的カーソル:3をセット
    recs.Open "顧客リスト", conect, 1
    
    '｢フィールド名」を取得しセルに入力する
    For i = 0 To recs.Fields.Count - 1
        Cells(1, i + 1).Value = recs.Fields(i).Name
    Next
    
    'Accessのデータベースから取得したデータをセルA2以降にコピー
    Cells(2, 1).CopyFromRecordset recs
    
    'レコード数を取得/メッセージボックス表示
    'RecordCountを使用するには上述のようにカーソル設定が必要
    '設定しない場合はRecordCountを使用しても-1が返される
    MsgBox "「顧客リスト」テーブルデータ数 : " & recs.RecordCount
    
    'データベースとの接続解除
    conect.Close
    'ワークシートA～G列の列幅を自動調整
    Columns("A:G").AutoFit
    
End Sub

全体的な流れとしては「データベースに接続」して「データを取得」、その後に「ワークシートにコピーする」「データベースとの接続解除」という流れになります。

◆データベースに接続

外部データベースを操作するにはActiveX Data Objects（以降ADO表記）を使用します。ADOとはOfficeアプリケーションと外部データベースとの接続や外部データベースから取得したデータの操作などを行うことができるプログラミングインターフェースです。

エクセルVBAでADOを使用するにはADOライブラリファイルへの参照が必要になるので、エディタの「ツール」⇒「参照設定」から「Microsoft ActiveX Data Objects 6.1 Library」を追加します。

これでADOを使用できますが、使用するには先ず接続確立用のConnectionオブジェクトとレコード取得用のRecordsetオブジェクトをそれぞれ生成する必要がありますので、Dimステートメントで宣言してセットしていきます。

Dim conect As Object, recs As Object

Set conect = CreateObject(“ADODB.Connection”)
Set recs = CreateObject(“ADODB.Recordset”)

オブジェクト作成後、Openメソッドを利用して外部データベースに接続していきます。

構文：object.connection.Open ConnectionString,UserID,Password,Options

OpenメソッドはobjectにConnectionオブジェクト（本サンプルでは変数conect）を指定することでデータベースに接続することができます。

サンプルでは同一保存場所にあるアクセスのデータベース「SampleDatabase1.accdb」ファイルに接続します。

conect.Open “Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=” & ThisWorkbook.Path & “\SampleDatabase1.accdb”

≪Openメソッド引数≫

引数	内容
ConnectionString	省略可、接続情報の指定
UserID	省略可、接続確立時に使用するユーザー名指定
Password	省略可、接続確立時に使用するパスワード指定
Options	省略可、接続確立後（同期）と確立前（非同期）のどちらで終了するか決定するConnectOptionEnum値を指定

≪引数ConnectionStringで使用する変数名≫

引数	内容
Provider	接続用のプロバイダ名を指定
Data Source	接続する外部データベースの格納場所（ファイルパス）を指定
Remote Provider	クライアント側の接続を開く際に使用するプロバイダ名を指定（RDSのみ）
Remote Server	クライアント側の接続を開く際に使用すサーバーパス名を指定（RDSのみ）
URL	ファイルやディレクトリなどのリソースを識別する絶対URLとして接続文字列を指定

サンプルコードコメント内にも記載していますが、参考までに変数Providerに指定する主な外部データベースのプロバイダー名は以下になります。

外部データベース	プロバイダー名
Access2016/2013/2010/2007	Microsoft.ACE.OLEDB.12.0
Access2003/2002	Microsoft.Jet.OLEDB.4.0
SQLServer	SQLOLEDB.1
Oracle	MSDAORA
DB2	IBMDADB2
ODBC接続	MSDASQL.1

◆データベースからデータを取得

データベースのデータを取得するのも上に書いた接続時と同様でOpenメソッドを利用します。

サンプルコード冒頭に生成したRecordsetオブジェクトのrecsを先程の変数conectの接続を使用してOpenメソッドで開き、「顧客リスト」のテーブルに入力されている全てのデータを変数recsに格納します。

recs.Open “顧客リスト”, conect, 1

末尾の1は最後にテーブルデータ数を取得する為に設定するCursorTypeで1または3をセットすることでカウントできます。このCursorTypeは本題に対してプラスα要素なので特に気にしなくても良いです。

◆エクセルのワークシートへコピー

接続したアクセスのデータベースから取得したデータをExcelのWorksheetへ入力（コピー）していきます。

先ずはRecordsetオブジェクトに格納されたデータからフィールド名を取得してセルへ入力していきます。

For i = 0 To recs.Fields.Count – 1
Cells(1, i + 1).Value = recs.Fields(i).Name
Next

次にデータをコピーしますが、Recordsetオブジェクトの全てのレコードをワークシートにコピーするにはCopyFromRecordsetメソッドを利用します。

構文：CopyFronRecordset（Data,MaxRows,MaxColumns）

≪CopyFromRecordsetメソッドの指定値≫

値	内容
Data	セル範囲にコピーするRecordsetオブジェクトを指定
MaxRows	省略可、ワークシートへコピーする最大数の指定で省略した場合はRecordsetオブジェクトの全てのレコードをコピーする
MaxColumns	省略可、ワークシートへコピーするフィールドの最大数の指定で省略した場合はRecordsetオブジェクトの全てのフィールドをコピーする

Cells(2, 1).CopyFromRecordset recsと記述することで、SheetのセルA2を左上端としたセル範囲にコピーされます。

◆レコード数のメッセージボックス表示

Recordsetオブジェクトのレコード数を取得するにはRecordCountプロパティを利用します。

構文：object.RecordCount

但し、このプロパティを利用する際は上にも少し書いたようにカーソルタイプを設定しておく必要があり、Recordsetを開く際にキーセットカーソルまたは静的カーソルをセットしておきます。

その上でMsgBox “「顧客リスト」テーブルデータ数 : ” & recs.RecordCountと記述すれば、メッセージボックスでレコード件数を表示できます。（カーソルセット未設定時は-1が返されます）

◆データベースとの接続解除

接続の解除はCloseメソッドを使用します。

構文：object.Close

このCloseメソッドはobjectに指定したConnectionオブジェクトとの接続を解除しますのでconect.Closeとします。

ここまでで一連の流れとしては終了なので、後はコピーされたデータを整理する上でセル範囲を指定して列幅を自動調整しています。

Columns(“A:G”).AutoFit

サンプルコードは以上になります。

◆参考：コピーを複数シートに分割する場合

例えばデータ数が膨大な場合など、コピー先を1つにSheetではなく複数Sheetに分割したいこともあるかと思います。簡易的なものですが一例としてSheet1とSheet2に分ける場合は以下になりますので、ご参考までに。

Sub Sample2()
  Dim conect As Object, recs As Object
  Dim temp As Variant
  Dim i As Long, j As Long

  Set conect = CreateObject("ADODB.Connection")
  Set recs = CreateObject("ADODB.Recordset")
  
  conect.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
  & "Data Source=" & ThisWorkbook.Path & "\SampleDatabase1.accdb"
  
  recs.Open "顧客リスト", conect
  
  '例：Sheet1とSheet2に分ける
  For i = 1 To 2
    With ThisWorkbook.Worksheets(i)
      For j = 0 To recs.Fields.Count - 1
        .Cells(1, j + 1).Value = recs.Fields(j).Name
      Next
      temp = .Cells(2, 1).CopyFromRecordset(recs, 5)
      recs.MoveFirst  '先頭のレコード
      recs.Move 5     '移動するレコード数
    End With
  Next
  conect.Close
End Sub

このコードを実行すると元のデータベースレコードが10件なので、Sheet1に5件、Sheet2に5件コピーを実行しています。