AccessのVBAでOLEオートメーションを使用してExcelのワークシートにAccessのテーブルやクエリーのデータを書き出す(エクスポートする)VBAのサンプルプログラム

2014.10.08

Access VBA:AccessのVBAでOLEオートメーションを使用してExcelのワークシートにAccessのテーブルやクエリーのデータを書き出す(エクスポートする)VBAのサンプルプログラム

AccessのVBAでOLEオートメーションを使用してExcelのワークシートにAccessのテーブルやクエリー(SQLの実行結果も含む)のデータを書き出す(エクスポート)するVBAのサンプルプログラムを覚書。

この記事のVBAのサンプルプログラムは、新規にExcelブックを作成してAccessのデータを書き出す方法について説明。

既存のExcelファイルにAccessデータを書き出すVBAのサンプルプログラムについては、別記事で作成予定。

スポンサーリンク

新規にExcelのワークブックにAccessのデータを書き出す(エクスポート)する場合

以下に記載するサンプルプログラムは、AccessのVBAでOLEオートメーションを使用してExcelの新規のワークブックを作成してそのブックのワークシートにAccessのテーブルやクエリー、SELECT文を用いたSQLの実行結果を書き出す(エクスポート)するVBAのサンプルプログラム。

プログラムを実行するための準備

プログラムを実行する前に以下の準備をしてください。

使用するオブジェクトライブラリの参照設定を行う

AccessのVBAでOLEオートメーションを使用してExcelを操作する場合、Excelのオブジェクトライブラリを参照設定する必要があります。

また、紹介するサンプルプログラムでは"Microsoft ActiveX Data Objects"(ADO)ライブラリを使用していますので、合わせて参照設定しておく必要があります。

参照設定方法

以下の2つのオブジェクトライブラリを参照設定する方法を以下に記載します。

Accessのリボンの[作成]タブをクリックする。

[作成]タブの右端の[標準モジュール]をクリックする。

Microsoft Visual Basic for Applications エディターが開くので、[ツール]メニューをクリックする。

[ツール]メニューの[参照設定]をクリックする。

[参照設定]ダイアログボックスが開くので、以下の2つのオブジェクトライブラリのチェックをオンにする。(XXはバージョンによって異なります)

  1. Microsoft Excel XX Object Library
  2. Microsoft ActiveX Data Objects XX Library

参照設定

[参照設定]ダイアログボックスの[OK]をクリックしてダイアログボックスを閉じます。

以上で参照設定は終了です。

サンプルデータベースの「ノースウィンド」データベースを使用する

この記事で紹介するVBAのサンプルプログラムは、Accessのテンプレートにあるノースウィンドデータベースを使用しています。

ノースウィンドデータベースで標準モジュールを新規に作成し、VBAのサンプルプログラムを貼り付けて実行すればすぐに動作が確認できますので、ノースウィンドデータベースを使用してください。

CopyFromRecordsetを使用したVBAのサンプルプログラム

ExcelのCopyFromRecordsetメソッドを使用した場合のVBAのサンプルプログラム。

Sub AccessToXL_Sample01()
Dim con As New ADODB.Connection, rec As New ADODB.Recordset
Dim xl As Excel.Application, xlwb As Excel.Workbook, xlsh As Excel.Worksheet
    '現在開いているデータベースと接続する
    Set con = CurrentProject.Connection
    
    'Excelにエクスポートしたいテーブルのデータを取得
    rec.Open "受注", con
    
    'Excelのインスタンスを作成
    Set xl = CreateObject("Excel.Sheet").Application
    
    'ExcelのWorkbookオブジェクトを作成
    Set xlwb = xl.Workbooks.Add
    
    'ExcelのWorksheetオブジェクトを作成
    Set xlsh = xlwb.Worksheets("sheet1")
    
    'Excelを表示
    xl.Application.Visible = True
    
    'エクスポートしたいテーブルのフィールド名をExcelのA1セルを起点として書き出し
    For i = 0 To rec.Fields.Count - 1
        xlsh.Cells(1, i + 1).Value = rec.Fields(i).Name
    Next i
    
    'フィールド名の下の行(2行目)から実際のデータを書き出す。
    xlsh.Range("a2").CopyFromRecordset rec
    '作成した各オブジェクトを開放
    Set xl = Nothing
    Set xlwb = Nothing
    Set xlsh = Nothing
    
End Sub

プログラム中のコメントを見ていただければ特に説明は必要ないと思いますので割愛します。

For/Doループを使用したVBAのサンプルプログラム

CopyFromRecordsetメソッドはあまり好きではありません。

理由は後述しますが、この手の処理のプログラムを作成する場合は、いつもForループとDo Untilを使用しています。

以下にCopyFromRecordsetを使用せずにForループとDo Untilの組み合わせでワークシートにデータを書き込むVBAのサンプルプログラムを記載します。

Sub AccessToXL_Sample02()
Dim con As New ADODB.Connection, rec As New ADODB.Recordset
Dim xl As Excel.Application, xlwb As Excel.Workbook, xlsh As Excel.Worksheet
    '現在開いているデータベースと接続する
    Set con = CurrentProject.Connection
    
    'Excelにエクスポートしたいテーブルのデータを取得
    rec.Open "受注", con
    
    'Excelのインスタンスを作成
    Set xl = CreateObject("Excel.Sheet").Application
    
    'ExcelのWorkbookオブジェクトを作成
    Set xlwb = xl.Workbooks.Add
    
    'ExcelのWorksheetオブジェクトを作成
    Set xlsh = xlwb.Worksheets("sheet1")
  
    'データを書き出す間は、Excelの描画処理を止めて処理速度を早める
    xl.Application.ScreenUpdating = False
  
    'エクスポートしたいテーブルのフィールド名をExcelに書き出し
    For i = 0 To rec.Fields.Count - 1
        xlsh.Cells(1, i + 1).Value = rec.Fields(i).Name
    Next i
    
    'エクスポートしたいテーブルのデータを書き出し
    j = 0
    Do Until rec.EOF
        For i = 0 To rec.Fields.Count - 1
            xlsh.Range("a2").Offset(j, i) = rec(i)
        Next i
        j = j + 1
        rec.MoveNext
    Loop
    
    'Excelを表示
    xl.Application.ScreenUpdating = True
    xl.Application.Visible = True
    
    '作成した各オブジェクトを開放
    Set xl = Nothing
    Set xlwb = Nothing
    Set xlsh = Nothing
End Sub

ForループとDo Untilを組み合わせているので、慣れないとわかりづらいプログラムかもしれません。

CopyFromRecordsetは、テーブルのデータをそのまま書き出す場合には、ループを使ったプログラムより圧倒的に処理速度が早いので、テーブルのデータをそのまま書き出すだけで良いならCopyFromRecordsetで良いと思います。

もし、書き出すテーブルのデータをExcelに書き出す際に加工処理をしたい場合にはループを使用したプログラムの方がデータ1つ1つをセルに書き出すのでその処理の中でデータの加工ができるというメリットが有ります。

ただし、ループを使用した処理は基本的に処理時間がかかるため、大量のデータを扱う場合は注意が必要です。

クエリーやSQL文(SELECT)の結果を使用したい場合

上記のVBAのサンプルプログラムは、Accessのテーブルを直接指定していますが、クエリー名を直接指定すればクエリーの結果セットを書き出せますし、SQL文を指定すればSQL文の実行結果を書き出すこともできます。

クエリーを指定する場合

以下のコードのテーブル名部分(受注)をクエリー名に変更します。

rec.Open "クエリー名", con

SQL文の実行結果を使用する場合

同様に以下のコードのテーブル名部分(受注)に実行するSQL文を指定します。

以下は、受注テーブルのレコードから[社員 ID]フィールドの値が"9"のデータのみ抽出した結果をExcelに書き出しています。

rec.Open "select * from 受注 where [社員 ID] = 9", con

CopyFromRecordsetがあまり好きではない理由

CopyFromRecordsetがあまり好きではない理由は以下のとおりです。

データの加工ができない

CopyFromRecordsetは、上記で説明したとおり単純なコピー&ペーストと同じ処理なので、Excelのセルに書き出す際にデータの加工処理が一切できません。

Accessのテーブルやクエリー、SQLの実行結果をExcelに書き出したいけど、Excelに書き出すときはAccessのデータを基に加工処理を加えた上で書き出したいという場合が多いので、そういう場合CopyFromRecordsetは、「使えない」メソッドとなってしまいます。

また、レコードの一部の項目(列)を省きたい場合にもそういった指定ができません。

そのために新たにクエリーを作って対応することになります。

私にとってはあまり「使えない」メソッドという印象です。

もちろんAccess側ですべてのデータ加工をした上で書き出せばよいのですが、事情によりそれができない場合が多かったので...

書き出せるデータ量に制限がある

CopyFromRecordsetで書き出せるデータには制限があります。

具体的な制限値はきちんと調べていませんが、以前かなり大量なデータをExcelに直接書き出すプログラムを作った際にすべてのデータが書き出せなかった経験があります。

特にエラー等も出なかったため、データが少ない理由がすぐに分からず苦労しました。

その時の印象が強いせいもあります。

Access VBAからExcelデータを書き出すVBAサンプルプログラムまとめ

今回の記事では、AccessのVBAでExcelのオブジェクトをOLEオートメーションを使用して新規のExcelファイルにAccessのデータの書き出しを実行するVBAのサンプルプログラムを紹介しました。

別の記事で既存のExcelファイルに同じようにAccessのデータを書き出しVBAのサンプルプログラムを紹介する予定です。

コメント

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