Access VBA Excelの複数のシートにデータをエクスポートする方法

今回は、Accessデータベースに保存されているデータを項目別にExcelのシート単位でエクスポートする方法を紹介します。

例えば、書籍情報を管理するために用意されたAccessデータベースから区分別にExcelのシート単位でデータを出力する方法を考えてみます。

サンプルテーブル
VBAの実装
VBAの実行によるエクスポート結果の確認
まとめ

サンプルテーブル

書籍情報を管理するテーブルとして「T_図書リスト」を用意します。

そして、「タイトル」、「区分」及び「著者」フィールドを作成し、いずれも「短いテキスト」のデータ型にします。

テーブルには表1に示すサンプルデータを登録します。

表1　「T_図書リスト」のデータ

タイトル	区分	著者
吾輩は猫である	小説	夏目漱石
はらぺこあおむし	絵本	エリック・カール
走れメロス	小説	太宰治
雪国	小説	川端康成
だるまさんが	絵本	かがくいひろし
ドラえもん	漫画	藤子不二雄
サザエさん	漫画	長谷川町子
破戒	小説	島崎藤村
砂の器	小説	松本清張
鉄腕アトム	漫画	手塚治虫

次に、Excelファイルに区分ごとでシートを分けたデータエクスポートをVBAで実装します。

VBAの実装

まずは、以下に示すVBAコードを標準モジュールに追加しましょう。

Public Sub ExcelExport()
On Error GoTo Err_EE

'変数宣言
Dim objExcel
Dim objSelection
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim mySQL As String
Dim i As Integer
Dim Classification() As Variant
Dim j As Integer

'T_図書リストの区分を配列に格納
Classification = Array("小説", "絵本", "漫画")

'エクセルオブジェクトを作成します
Set objExcel = CreateObject("Excel.Application")

'エクセル画面を表示しない設定とします
objExcel.Visible = False

'ワークブックを新規に作成し、ワークシートを2つ追加します。
objExcel.Workbooks.Add
objExcel.ActiveWorkbook.Worksheets.Add
objExcel.ActiveWorkbook.Worksheets.Add

'ワークシート分の処理を繰り返す
For j = 0 To 2
    
    'j+1番目のワークシートをセット
    Set objSelection = objExcel.Workbooks(1).Worksheets(j + 1)
    
    With objSelection
        
        'ADOでカレントデータベースの接続を開始します。
        Set cn = CurrentProject.Connection
    
        'SQL文のセット
        mySQL = "SELECT T_図書リスト.[タイトル], T_図書リスト.著者 FROM T_図書リスト WHERE (((T_図書リスト.区分)='" & Classification(j) & "'));"
        
        '作成したSQLに基づきレコードセットを開く
        rs.Open mySQL, cn, adOpenKeyset, adLockOptimistic
        
        'Excelにエクスポートする際にタイトル行を表示するための処理
        For i = 1 To rs.Fields.Count
          .Cells(1, i) = rs.Fields(i - 1).Name
        Next
        
        'ワークシートの2行目にデータコピー
        .Range("A2").CopyFromRecordset rs
        
        'ワークシートの名称変更
        .Name = Classification(j)
 
        '列幅の調整
        .Columns("A:B").AutoFit

        'タイトル行の背景色をグレーにする
        .Range("A1:B1").Interior.Color = RGB(217, 217, 217)

        'レコードセットを閉じる
        rs.Close
        mySQL = ""

    End With

    Set objSelection = Nothing

Next

'ADOによる接続を解除
cn.Close
Set cn = Nothing

'Excelファイルの保存
objExcel.ActiveWorkbook.SaveAs "C:\TEST\図書リスト.xlsx"
objExcel.Workbooks.Close
objExcel.Quit
 
'エクセルオブジェクトの破棄
Set objExcel = Nothing

If MsgBox("ファイルを保存しました。作成したファイルを開きますか?", vbYesNo) = vbYes Then
    Shell "Excel.exe " & Chr(&H22) & "C:\TEST\図書リスト.xlsx" & Chr(&H22), vbNormalFocus 'Shellで作成したExcelを起動
End If

Exit_EE:
Exit Sub

Err_EE:
MsgBox Err.Description
Resume Exit_EE

End Sub

実装したVBAコードについて内容を確認していきましょう。

最初はExcelの操作やADOによるデータベース操作のための変数宣言をしています。

Classification配列変数は、このあとに続くFor文で区分単位の処理を効率良く実装するために用いています。配列変数に値を格納する場合は「Array」を使います。

CreateObjectでExcelオブジェクトを作成し、今回の場合はワークブックを新規に作成し、かつ、書籍の区分が3つあるためワークシートを2つ追加する処理を記載しています。

次に、For文中の処理を見ていきましょう。

まずは、ワークシートオブジェクトをセットした後、ADOでカレントデータベースに接続をし、開くレコードセットのSQL文を作成した後、これを開いています。

レコードセットの全ての情報をコピーするためには「CopyFromRecordset」を用いますが、タイトル行はコピーされないため、予めその処理を行います。「rs.Fields.Count」でフィールド数を数えた後、各フィールド名を取得しExcelワークシートの1行目に値をコピーしています。

レコードセットの情報をコピーするための「CopyFromRecordset」メソッドについて確認します。

CopyFromRecordset (Data, MaxRows, MaxColumns)

引数については、表2に示すとおりです。

表2　CopyFromRecordsetメソッドの引数

引数	要否	データ型	説明
Data	必須	バリアント型	セル範囲にコピーする Recordset オブジェクトを指定します。
MaxRows	省略可能	バリアント型	ワークシートにコピーするレコードの最大数を指定します。この引数を省略すると、Recordset オブジェクトのすべてのレコードをコピーします。
MaxColumns	省略可能	バリアント型	ワークシートにコピーするフィールドの最大数を指定します。この引数を省略すると、Recordset オブジェクトのすべてのフィールドをコピーします。