【Access】AccessVBAでExcelファイル出力する方法

AccessVBAでExcel出力を行う方法を紹介します。

Excelテンプレートを作成

まずは、出力したいExcelをイメージします。
そのExcel出力したいものの中で既に決まっているもの（ラベル）を整えます。

一覧形式に出力したい場合

出力するヘッダーにラベルを設定しておきます。
その他、フォントや色なども予め固定で設定できるものは設定しておきます。

AccessVBAでExcel操作

AccessVBAでExcel操作をするには、必要なことはいくつかあります。

参照設定

ツール＞参照設定

Microsoft Excel XXX Object Library ※XXXはバージョンにより変わります

「xlContinuous」などエクセルの関数を使用するとエラーになります。

テーブルのデータをExcel出力

テーブルデータをExcel出力する方法を紹介します。
実際は他にも方法があります。Excel出力ではなくPDFに出力したり
データを上手く出力できるようになると非常に便利です。
Accessのレポートで出力する場合は、印刷かPDFとなりますが
Excelに出力したい場合も多いと思います。
その場合は、テンプレート作成して必要な条件のデータを出力するという事が可能です。

良くあるのは、一年間で使用した経費を集約してExcel出力する等
Excelと違ってAccessはデータ処理が強いので上手くデータを整理して使う事ができます。

Private Sub cmd出力_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlApp   As Object
Dim xlBook  As Object
Dim xlSheet As Object
Dim cnt As Integer
Dim strSql As String
Dim strAnkenListPath As String

On Error GoTo Error

   ' テンプレートファイル
    strAnkenListPath = MyPath & "\tmp\案件情報一覧表.xlsx"
    
    Set db = CurrentDb
    
    ' 出力元データ
    strSql = "SELECT IIf([new_flg]=True,'新着', IIf([status]='4','消滅',IIf([status]='9','終了',IIf([notice_flg]=true,'注目','')))) AS newflg,"
    strSql = strSql & " t_anken.anken_no,"
    strSql = strSql & " t_anken.kian_date,"
    strSql = strSql & " t_anken.anken_nm,"
    strSql = strSql & " t_anken.anken_gaiyo,"
    strSql = strSql & " t_anken.gyoshu,"
    strSql = strSql & " t_anken.yakwari,"
    strSql = strSql & " t_anken.tanka_soto,"
    strSql = strSql & " t_anken.seisan,"
    strSql = strSql & " t_anken.sagyo_basho,"
    strSql = strSql & " t_anken.kaishi_jiki,"
    strSql = strSql & " t_anken.kikan,"
    strSql = strSql & " t_anken.boshu_ninzu,"
    strSql = strSql & " t_anken.nenrei_seigen,"
    strSql = strSql & " t_anken.mendan_kaisu,"
    strSql = strSql & " t_anken.nationality,"
    strSql = strSql & " t_anken.foreign_lang,"
    strSql = strSql & " t_anken.hissu_skill,"
    strSql = strSql & " t_anken.others_inf,"
    strSql = strSql & " t_anken.biko"
    strSql = strSql & " FROM t_anken INNER JOIN m_kokyaku ON t_anken.kokyak_id = m_kokyaku.kokyak_id"
　　' 条件がある場合は抽出条件を（ここでは省略）
'    strSql = strSql & pAnkenWhere
    strSql = strSql & "    ORDER BY t_anken.new_flg, t_anken.anken_no DESC;"

    ' データ取得（rsオブジェクトにレコードセットが格納されます）
    Set rs = db.OpenRecordset(strSql)
    ' 繰り返し全件処理します
    If rs.EOF = False Then
        rs.MoveLast
        cnt = rs.RecordCount + 1
        
　　　　' インスタンスの生成
        Set xlApp = CreateObject("excel.application")          　　
　　　　' EXCELブックを開く 
        Set xlBook = xlApp.Workbooks.Open(strAnkenListPath)     
　　　　' シートを変数に設定
        Set xlSheet = xlBook.Worksheets(1)                      

        rs.MoveFirst
        xlSheet.Cells(2, 1).CopyFromRecordset rs
        xlSheet.Range("A1:T" & cnt & "").Borders.LineStyle = xlContinuous

        ' EXCELを保存
        xlApp.ActiveWorkbook.SaveAs MyPath & "excel\案件情報一覧表_" & Format(Now(), "yyyymmddhhnnss") & ".xlsx"
        ' EXCELを表示
        xlApp.Visible = True
        
　　　　' 解放（これやらないとタスクマネージャーに処理残ります）
        Set xlSheet = Nothing
        Set xlBook = Nothing
        Set xlApp = Nothing
    End If
    
    Set rs = Nothing
    Set db = Nothing


Exit Sub
Error:
    MsgBox "エラーが発生しました。エラー内容：" & Err.Description & Chr$(10), vbCritical, "案件一覧出力"
End Sub

実際に動作確認

(1)Accessフォームにボタンを追加

(2)Excel出力ボタンをクリック

テーブルデータが出力される
セルの背景色は、A列の文字をみて背景色設定をテンプレートに埋め込みしています

セルの条件書式で背景色設定

テンプレートで条件書式を設定しておきます