PR

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

VBA

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

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

スポンサーリンク

サンプルテーブル

書籍情報を管理するテーブルとして「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 (DataMaxRowsMaxColumns)

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

表2 CopyFromRecordsetメソッドの引数

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

今回の場合は、Excelワークシートの「A2」セルにCopyFromRecordsetメソッドで取得したレコードセットの全てのデータをコピーする処理としています。

その後は、ワークシート名の変更、列幅の自動調整、タイトル行の背景色をグレーにする処理をし、作成したExcelファイルを保存する処理としています。

以上の処理をワークシート分(書籍区分の数だけ)行い、ループ文を抜けた後はADOによるデータベース接続の解除をします。

ファイルは「C:\TEST」フォルダに「図書リスト.xlsx」として保存するようにしています。保存後、Excelファイルは閉じ、Excelオブジェクトも破棄しています。

最後に、作成したExcelファイルを開くか確認し、開く場合はこれを開く処理をShell関数を用いて実装しています。

以上が、VBAで実装した処理の内容となります。

VBAの実行によるエクスポート結果の確認

早速作成したVBAによる処理を実行し、Excelファイルが作られるかを確認しましょう。

処理を実行すると、C:\TESTフォルダに新たに「図書リスト.xlsx」ファイルが作成され、これを開くと図1に示す結果が得られることが確認できます。

図1 Excelファイルへのエクスポート結果

このように、区分に応じてデータを分類し、Excelの別々のワークシートにコピーできるようになりました。

まとめ

今回は、Accessデータベースに保存されているデータを項目別にExcelのシート単位でエクスポートする方法を紹介しました。この方法で定型化された処理を自動で行えるようにすることで、業務効率を大幅に改善することができますので、参考にしていただければと思います。

今の時代、VBAに限らずプログラミングができるということは、当然のスキルとして広く社会で認知されています。いまだプログラミングが十分にできないという人は、今からでも遅くありません。できるだけ早く基本的なプログラミングスキルを習得することをお勧めします。

最初につまずきがちな点も、書籍を読みながら試行錯誤して課題解決していくことで、自身のスキルアップを目指すことができます。思い立ったが吉日、是非業務効率の改善に向けてプログラミング学習に勤しんでください!

スポンサーリンク

コメント

タイトルとURLをコピーしました