【ExcelVBA】ExcelのマクロでAccessテーブルのデータをExcelファイルに出力・保存する方法とは

この記事では、ExcelのマクロでAccessテーブルのデータをExcelファイルに出力・保存する方法についてご説明します。

【動画】ExcelのマクロでAccessテーブルのデータをExcelファイルに出力・保存する実際の動き

本題に入る前に、まずは次の動画をご覧ください。


ExcelのマクロがAccessテーブルに対して条件に合致するデータをSELECT文を実行して抽出し、新規作成したExcelファイルのシートに抽出したデータを出力して保存しています。

マクロ作成の流れ

STEP.1
ExcelのマクロがAccessファイルに接続する
ExcelのマクロがAccessファイルに接続します。
STEP.2
条件に合致したデータを抽出するSELECT文を用意して実行する
条件に合致したデータを抽出するSELECT文を用意して実行します。
STEP.3
新規でExcelファイルを開く
新規でExcelファイルを開きます。
STEP.4
STEP.2でSELECT文実行後に抽出されたデータをSTEP.3で開いたExcelファイルのシートに貼り付ける
STEP.2でSELECT文実行後に抽出されたデータをSTEP.3で開いたExcelファイルのシートに貼り付けします。
STEP.5
STEP.4のExcelファイルを名前を付けて保存する
STEP.4のExcelファイルを名前を付けて保存します。
STEP.6
STEP.5のExcelファイルを閉じる
STEP.5のExcelファイルを閉じます。

Excelファイルの例

今回は次のExcelファイルを用意しました。

「termVal」(A2のセル)には条件に指定する値(今回のサンプルは月)を入力し、「saveDataPath」(A5のセル)には取得したデータを出力したExcelファイルをどこに保存するかを指定します。

コードの例

Excelのマクロのコード(例)

Option Explicit

Sub test()

    Dim DBName          As String                   'データベース名
    Dim tblNM           As String                   '取得元のテーブル名
    Dim connDB          As String                   'データベース接続情報
    Dim sqlStr          As String                   'SQL文
    Dim cnt             As Long                     'カウンタ
    Dim conn            As ADODB.Connection         'Connection用変数
    Dim rs              As ADODB.Recordset          'レコードセット用変数
    Dim newWB           As Workbook                 '新規作成するExcelファイル用変数
    Dim crntPath        As String                   '本Excelファイルの格納先
    Dim termVal         As String                   '検索条件の値
    Dim saveDataPath    As String                   '抽出したデータの保存先
    
    '抽出したデータの保存先を取得する
    saveDataPath = Worksheets("work").Range("saveDataPath").Value
    
    '検索条件の値を取得する
    termVal = Worksheets("work").Range("termVal").Value
         
    'カレントディレクトリのデータベースパスを取得
    DBName = ThisWorkbook.Path & "\" & "0194.mdb"

    'データを取得するテーブル名を取得
    tblNM = "tbl_score_list"
    
    'Connectionインスタンスの生成
    Set conn = New ADODB.Connection

    'データベース接続情報の取得
    conn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source=" _
                            & DBName & ""
    
    'Accessに接続する
    conn.Open connDB
 
    'Recordsetオブジェクトのインスタンスを生成する
    Set rs = CreateObject("ADODB.Recordset")
    
    'クライアントサイドカーソルに変更
    rs.CursorLocation = 3
        
    'データを取得するSELECT文を用意する
    sqlStr = "select *"
    sqlStr = sqlStr & " from "
    sqlStr = sqlStr & " " & tblNM
    sqlStr = sqlStr & " where"
    sqlStr = sqlStr & " months"
    sqlStr = sqlStr & " ="
    sqlStr = sqlStr & " '" & termVal & "'"
    
    'SELECT文を実行してRecordsetを開く
    rs.Open sqlStr, conn, adOpenDynamic
    
    If rs.RecordCount = 0 Then
    
        'データ件数が0件の場合
    
        MsgBox "データが存在しません"
    
        '処理を終了する
        Exit Sub
    
    Else
                                                                                
        'データが1件以上存在する場合
        
        '取得したデータを貼り付けるための新たなExcelファイルを作成する
        Set newWB = Workbooks.Add
                    
        '表の列数分だけ処理をループする
        For cnt = 0 To rs.Fields.Count - 1
        
            '表の列名をセルに出力する
            newWB.Worksheets(1).Cells(1, cnt + 1).Value = rs.Fields.Item(cnt).Name
            
        Next cnt
    
        '先ほど貼り付けた列名の下の行にデータを貼り付ける
        newWB.Worksheets(1).Cells(2, 1).Offset(0, 0).CopyFromRecordset rs
        
        '新規作成したExcelファイルに名前を付けて保存する
        Call newWB.SaveAs(saveDataPath & "\" & termVal & ".xlsx")
        
        '新規作成したExcelファイルを閉じる
        newWB.Close
    
    End If
    
    '各終了処理
    conn.Close
    
    '後処理
    Set rs = Nothing
    Set conn = Nothing

End Sub

注目すべきコード①

最初に見て頂きたいのは18行目から27行目です。

    '抽出したデータの保存先を取得する
    saveDataPath = Worksheets("work").Range("saveDataPath").Value
    
    '検索条件の値を取得する
    termVal = Worksheets("work").Range("termVal").Value
         
    'カレントディレクトリのデータベースパスを取得
    DBName = ThisWorkbook.Path & "\" & "0194.mdb"

    'データを取得するテーブル名を取得
    tblNM = "tbl_score_list"

以上のコードは、Accessのデータベースの在り処やテーブル名、Excelファイルの保存先などを取得するコードです。

18行目は抽出したデータの保存先、21行目は検索条件の値、24行目はデータの取得先のAccessデータベースファイル、27行目はデータを抽出するAccessデータベースファイルのテーブル名です。

注目すべきコード②

次に見て頂きたいのは30行目から38行目です。

    'Connectionインスタンスの生成
    Set conn = New ADODB.Connection

    'データベース接続情報の取得
    conn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source=" _
                            & DBName & ""
    
    'Accessに接続する
    conn.Open connDB

以上のコードは、ExcelがAccessにADO接続する接続情報を取得してAccessに接続している処理のコードになります。

ADO接続するために必要なConnectionインスタンスを30行目で生成し、そのConnectionインスタンスに対してADO接続するために33行目から35行目で接続情報を設定します。

注目すべきコード③

次に見て頂きたいのは47行目から56行目です。

    'データを取得するSELECT文を用意する
    sqlStr = "select *"
    sqlStr = sqlStr & " from "
    sqlStr = sqlStr & " " & tblNM
    sqlStr = sqlStr & " where"
    sqlStr = sqlStr & " months"
    sqlStr = sqlStr & " ="
    sqlStr = sqlStr & " '" & termVal & "'"
    
    'SELECT文を実行してRecordsetを開く
    rs.Open sqlStr, conn, adOpenDynamic

以上のコードは、データを取得するSELECT文を用意して実行し、データを取得する処理のコードです。

47行目から53行目でデータを取得するSELECT文を用意し、56行目のOpenメソッドの引数にSELECT文を指定して実行することでデータを取得することができます。

取得したデータはRecordsetに格納されます。

注目すべきコード④

次に見て頂きたいのは58行目です。

    If rs.RecordCount = 0 Then

以上のコードは、取得したデータの件数を判定しているIfを使ったコードです。

RecordCountプロパティの値はデータの件数ですが、この件数が0かそうでないかを確認しています。

注目すべきコード⑤

次に見て頂きたいのは72行目です。

        '取得したデータを貼り付けるための新たなExcelファイルを作成する
        Set newWB = Workbooks.Add

以上のコードは、新たにExcelファイルを開くコードです。

WorkbooksオブジェクトのAddメソッドを実行すると、新たにExcelファイルを開きます。

注目すべきコード⑥

次に見て頂きたいのは75行目から80行目です。

        '表の列数分だけ処理をループする
        For cnt = 0 To rs.Fields.Count - 1
        
            '表の列名をセルに出力する
            newWB.Worksheets(1).Cells(1, cnt + 1).Value = rs.Fields.Item(cnt).Name
            
        Next cnt

以上のコードは、取得したデータをセルに出力する処理と、そのヘッダ(列名)をセルに出力する処理を行っているコードです。

75行目から80行目では、表の列の数だけ繰り返しヘッダ(列名)をセルに出力しています。

78行目のFields.Item.Nameプロパティからヘッダ(列名)を取得することができます。

ヘッダ(列名)をセルに出力し終わったら83行目で、ヘッダ(列名)の1つ下のセル位置に結合させたデータをセルに貼り付けています。

注目すべきコード⑦

次に見て頂きたいのは86行目から89行目です。

        '新規作成したExcelファイルに名前を付けて保存する
        Call newWB.SaveAs(saveDataPath & "\" & termVal & ".xlsx")
        
        '新規作成したExcelファイルを閉じる
        newWB.Close

以上のコードは、新規作成したExcelファイルに名前を付けて保存し、そのExcelファイルを閉じる処理のコードです。

86行目で新規作成したExcelファイルに名前を付けて保存しています。

なお、今回のサンプルでは、検索条件の値をファイル名に使っています。

例えば検索条件の値が「6月」の場合は、「6月.xlsx」とExcelファイルが作成されます。(Excelファイルの拡張子は「xlsx」を指定)

動作確認

マクロ実行前

今回は「Excelファイルの例」および「注目すべきコード⑦」のexcelファイルを使い、月が「6月」を条件に指定します。

また、今回扱うAccessのデータベースの「tbl_score_list」テーブルのデータは次の通りです。

以上のmonths列が「6月」のデータ(赤枠のデータ)を取得します。

マクロ実行後

マクロが実行されると、下の通りExcelファイルが生成されました。

ファイル名は「6月.xlsx」で、ファイルの中身を見てみるとAccessのデータベースの「tbl_score_list」テーブルにある「months」が6月のデータがシートに出力されていることが確認できます。

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

  1. Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、先ほどのコードの10行目の「ADODB.Connection」と11行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。

    Dim conn            As ADODB.Connection         'Connection用変数
    Dim rs              As ADODB.Recordset          'レコードセット用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

最後に

本記事では、ExcelのマクロでAccessテーブルのデータをExcelファイルに出力・保存する方法についてご説明しました。

AccessテーブルのデータをExcelファイルに出力・保存したい時は本記事を参考にしてみてくださいね。

Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら

Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。

Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。

→ 受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら