AccessからVBAでEXCELへ帳票出力する

当ページのリンクには広告が含まれています。
AccessからExcelへ
  • URLをコピーしました!

Accessから帳票を出力する一番一般的な方法は、Accessのレポートを使う方法です。

この方法の弱点はレポートの細かい編集が面倒だという点と、出力した形で保存できないという点です。(PDF形式で出力すれば保存は可能ですが・・・)

このため、AccessからExcelファイル形式で帳票を出力したいというニーズはかなり多くあります。

今日はAccessからVBAを使ってExcelファイルへ帳票ファイルを出力する方法をご紹介します。

個人の環境下で検証しておりますのでお使いの環境やデータによって正常に動作しない恐れもあります。サンプルはご自由にお使いいただいて結構ですが自己責任でご利用をお願いいたします。

目次

AccessからExcelへファイルを出力する方法

VBAを使ってACCESSからEXCELにファイル出力する方法はいくつかあります。
用途に応じて使い分けて使ってください。

  • Accessのエクスポートを利用して出力する
  • レコードセットを新しいEXCELファイルに貼り付ける
  • 体裁を整えたテンプレートファイルへレコードセットを出力する
  • 1行ずつデータを加工しながら出力する
簡単な順

エクスポート>レコードセット貼り付け>1行ずつデータ加工

細かい設定が可能な順

1行ずつデータ加工>レコードセット貼り付け>エクスポート

Accessのエクスポートを利用して出力する

一番簡単な方法です。
ACCESSのエクスポート機能を使って所定のファイルに出力します。

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, テーブル名(クエリー名), 出力ファイル名(フルパス), True, “”

第5引数ヘッダーを出力する場合はTrue, 不要の場合はFalse
第6引数インポートのときのみシート名を指定します。エクスポート時は指定不要

ただし、エクスポートする対象として指定できるのは、テーブル名またはクエリー名です。
このため、何らかの条件を指定してエクスポートしたい場合は、予めクエリーに条件値をセットしておく必要があります。

※一旦ワークテーブルに出力してからエクスポートする方法もあります。

また、ただ出力するだけなのでファイルの体裁を整えるような細かな制御はできません。

レコードセットをEXCELファイルに貼り付ける

こちらは取得したレコードセットの値をEXCELファイルに貼り付ける方法です。

データをまとめて一覧形式で出力する場合はこの方法が便利です。

レコードセットをEXCELに出力するサンプルコード

'------------------------------------------------------------
'   処理内容:Excel出力
'   引数:なし
'   戻り値:
'  更新:
'------------------------------------------------------------
Private Sub OutputExcel()
    Dim strsql          As String
    Dim strFileName     As String
    Dim xlapp           As Object
    Dim myCn            As New ADODB.Connection
    Dim myRs            As New ADODB.Recordset
    Dim colcnt          As Integer
    
    On Error GoTo Err_Exit
    
    'ファイル名作成
    strFileName = csOutputFileName & "_" & Format(Date, "yyyymmdd") & ".xlsx"
    
    'EXCELアプリケーションを起動
    Set xlapp = CreateObject("Excel.Application")
    
    Set myCn = CurrentProject.Connection
    
    strsql = "SELECT * FROM テストテーブル"
    
    'レコードセットオープン
    myRs.Open strsql, myCn, adOpenForwardOnly, adLockReadOnly
    
    With xlapp
        'セットする過程が見えないよう一旦不可視
        .Visible = False
        
        '新しいBookを追加
        .Workbooks.Add
        .Worksheets("Sheet1").Select
        
        'レコードセットのフィールド名(見出し)出力処理
        For colcnt = 0 To myRs.Fields.Count - 1
            .Worksheets("Sheet1").Cells(1, colcnt + 1).Value = myRs.Fields(colcnt).Name
        Next
            
        '結果値出力処理(1行目はヘッダーなので、2行目1列目からセット
        .Cells(2, 1).CopyFromRecordset myRs
        
        '列幅自動調整
        .Cells.EntireColumn.AutoFit
        
        '完了したら保存
        .ActiveWorkBook.SaveAs FileName:=strFileName
        
        MsgBox "出力しました。", vbOKOnly + vbInformation
        
    End With
    
    Set myRs = Nothing: Close
    Set myCn = Nothing: Close
    'Excelを終了します
    xlapp.Quit
    Exit Sub
    
Err_Exit:
    MsgBox Err.Number & ":" & Err.Description, vbOKOnly + vbCritical, "OutputExcel()"
    Set myRs = Nothing: Close
    Set myCn = Nothing: Close
    xlapp.Quit
    
End Sub

サンプルコードの解説

処理の概要

ADOのレコードセットをADOのCopyFromRecordsetメソッドを利用し、ワークシートの指定されたセルに貼り付けます。

今回のサンプルプログラムはあらかじめ決められたファイル名でEXCELファイルを保存して終了します。

出力先ファイル名

ファイルのフォルダや先頭の文字列はcsOutPutFileNameとして事前に定義してあります。

(例)Private Const csOutPutFileName = “C:\work\EXCEL出力”

これにアンダーバー+今日の日付を追加する設定です。

出力先:C:\work\EXCEL出力_yyyymmdd.xlsx

フィールド名(見出し)出力

CopyFromRecordsetメソッドはデータを出力するメソッドなので見出し行は含まれません。

このため、見出し行は別途出力する必要があります。

レコードセットのカラム数分ループしながら1行目に見出しとしてフィールド名をセットします。

列幅自動調整

おまけです。

ただ出力すると全項目の列幅が規定値になってしまい見づらいので、一気に列幅を調整できるEntireColumn.AutoFitを実行しています。

体裁を整えたテンプレートファイルへデータを出力する

新しいEXCELファイルではなく、あらかじめ用意したEXCELの雛形(テンプレート)にデータを出力することもできます。

この方法の利点は体裁を整えた形に出力するので、出力後加工なしでお客様に提出したり印刷したりできる点です。

Excelの雛形はできればテンプレート形式をおすすめします。(xltx, xltmなど)

テンプレート形式は通常のファイルとは異なり、開く操作をすると自分自身ではなく、そのコピーを開くようになっています。

コピーされたファイル名には「テンプレート名1」や「テンプレート名2」といった形で自動的に数字が付き、そのまま保存操作をしても元のテンプレートに上書きしてしまうことがありません。

つまり、ひな形であるファイルをうっかり上書きしてしまうといったミスを防ぐことができます。

テンプレートファイルを作る

まず「テンプレート.xltx(または.xltm)」ファイルを作成しましょう。(保存する際の拡張子を.xltxまたは.xltmにしてください。)

デザインは自由に作成してください。

テンプレートファイルに出力するサンプルプログラム

'------------------------------------------------------------
'   処理内容:Excel出力(テンプレートファイルへ出力)
'   引数:なし
'   戻り値:
'  更新:
'------------------------------------------------------------
Private Sub OutputTemplateExcel()
    Dim strsql          As String
    Dim strTemplate     As String
    Dim strFileName     As String
    Dim xlapp           As Object
    Dim myCn            As New ADODB.Connection
    Dim myRs            As New ADODB.Recordset
    
    On Error GoTo Err_Exit
    
    'ファイル名作成
    strFileName = csOutputFileName & "_" & Format(Date, "yyyymmdd") & ".xlsx"
    
    'EXCELアプリケーションを起動
    Set xlapp = CreateObject("Excel.Application")
    
    'セットする過程が見えないよう一旦不可視
    xlapp.Visible = False
    
    Set myCn = CurrentProject.Connection
    
    strsql = "SELECT * FROM テスト"
    
    'レコードセットオープン
    myRs.Open strsql, myCn, adOpenForwardOnly, adLockReadOnly
    
    With xlapp
        'テンプレートを開く
        strTemplate = Application.CurrentProject.Path & "\" & csOutputTemplate
        
        'テンプレートファイルが存在しないときはエラー
        If Dir(strTemplate) = "" Then
            MsgBox "テンプレートファイルを確認してください。", vbOKOnly + vbCritical, "エラー"
            .Visible = True
            .Quit
            Exit Sub
        End If
        
        'テンプレートファイルオープン
        .Workbooks.Open strTemplate
    
        '結果値出力処理(1行目にヘッダーを表示しているので、2行目1列目からセット
        .Cells(2, 1).CopyFromRecordset myRs
        
        '完了したら保存
        .ActiveWorkBook.SaveAs FileName:=strFileName
        
        MsgBox "出力しました。", vbOKOnly + vbInformation
    End With
    
    Set myRs = Nothing: Close
    Set myCn = Nothing: Close
    'Excelを終了します
    xlapp.Quit
    Exit Sub
    
Err_Exit:
    MsgBox Err.Number & ":" & Err.Description, vbOKOnly + vbCritical, "OutputExcel()"
    Set myRs = Nothing: Close
    Set myCn = Nothing: Close
    xlapp.Quit
    
End Sub

サンプルコードの解説

処理の概要

先の「レコードセットをEXCELに貼り付ける」と異なる点は、出力先があらかじめ用意したEXCELのテンプレートファイルであるという点です。

テンプレートはAccessファイルがあるフォルダと同じフォルダに用意しEXCEL起動時にテンプレートファイルを指定します。

ヘッダーをテンプレートに記述しておけるのでレコードセットデータを貼り付けるだけです。

テンプレートファイル名

テンプレートファイル名はcsOutputTemplateとして事前に定義してあります。

(例)Private Const csOutputTemplate= “テンプレート.xltx”

Accessファイルのカレントフォルダ(Application.CurrentProject.Path)にテンプレートファイルが存在しない場合はエラーとなります。

フィールド名(見出し)

今回はテンプレートの1行目に見出しを記述していますので、2行目からデータを貼り付けます。

レコードセットの取得フィールドとテンプレートの見出しフィールドは完全一致している必要があります。
CopyFromRecordsetメソッドを利用する場合はレコードセットを取得する際にテンプレートと合わせて置きましょう。

1行ずつデータを加工しながら出力する

これまでCopyFromRecordsetメソッドを使ってレコードセットの全てのデータを所定の位置に出力する方法をご紹介してきました。

実際はレコードセットのデータによって出力を出し分けたり、もっと細かい処理が必要な場合もあります。

そういった場合は、以下のようにレコードセットを1行ずつ処理しながら所定のセルに値をセットすることもできます。

'1行ずつ処理
If Not myRs.EOF Then
    myRs.MoveFirst
    Dim rowcnt As Integer
    rowcnt = 2
    While Not myRs.EOF
        '1列目にフィールドA
        .Cells(rowcnt, 1) = myRs.Fields("フィールドA")
        '2列目にフィールドB
        .Cells(rowcnt, 2) = myRs.Fields("フィールドB")
        rowcnt = rowcnt + 1
        myRs.MoveNext
    Wend
End If

よかったらシェアしてね!
  • URLをコピーしました!

コメント

コメントする

目次