Accessから帳票を出力する一番一般的な方法は、Accessのレポートを使う方法です。
この方法の弱点はレポートの細かい編集が面倒だという点と、出力した形で保存できないという点です。（PDF形式で出力すれば保存は可能ですが・・・）
このため、AccessからExcelファイル形式で帳票を出力したいというニーズはかなり多くあります。
今日はAccessからVBAを使ってExcelファイルへ帳票ファイルを出力する方法をご紹介します。
個人の環境下で検証しておりますのでお使いの環境やデータによって正常に動作しない恐れもあります。サンプルはご自由にお使いいただいて結構ですが自己責任でご利用をお願いいたします。
AccessからExcelへファイルを出力する方法
VBAを使ってACCESSからEXCELにファイル出力する方法はいくつかあります。
用途に応じて使い分けて使ってください。
- Accessのエクスポートを利用して出力する
- レコードセットを新しいEXCELファイルに貼り付ける
- 体裁を整えたテンプレートファイルへレコードセットを出力する
- １行ずつデータを加工しながら出力する
エクスポート＞レコードセット貼り付け＞1行ずつデータ加工
1行ずつデータ加工＞レコードセット貼り付け＞エクスポート
Accessのエクスポートを利用して出力する
一番簡単な方法です。
ACCESSのエクスポート機能を使って所定のファイルに出力します。
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, テーブル名（クエリー名）, 出力ファイル名（フルパス）, True, “”
| 第５引数 | ヘッダーを出力する場合はTrue, 不要の場合はFalse |
|---|---|
| 第６引数 | インポートのときのみシート名を指定します。エクスポート時は指定不要 |
ただし、エクスポートする対象として指定できるのは、テーブル名またはクエリー名です。
このため、何らかの条件を指定してエクスポートしたい場合は、予めクエリーに条件値をセットしておく必要があります。
※一旦ワークテーブルに出力してからエクスポートする方法もあります。
また、ただ出力するだけなのでファイルの体裁を整えるような細かな制御はできません。
レコードセットを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メソッドはデータを出力するメソッドなので見出し行は含まれません。
このため、見出し行は別途出力する必要があります。
レコードセットのカラム数分ループしながら１行目に見出しとしてフィールド名をセットします。
列幅自動調整
おまけです。
ただ出力すると全項目の列幅が規定値になってしまい見づらいので、一気に列幅を調整できる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）にテンプレートファイルが存在しない場合はエラーとなります。
フィールド名（見出し）
今回はテンプレートの１行目に見出しを記述していますので、２行目からデータを貼り付けます。
レコードセットの取得フィールドとテンプレートの見出しフィールドは完全一致している必要があります。
CopyFromRecordsetメソッドを利用する場合はレコードセットを取得する際にテンプレートと合わせて置きましょう。
１行ずつデータを加工しながら出力する
これまでCopyFromRecordsetメソッドを使ってレコードセットの全てのデータを所定の位置に出力する方法をご紹介してきました。
実際はレコードセットのデータによって出力を出し分けたり、もっと細かい処理が必要な場合もあります。
そういった場合は、以下のようにレコードセットを１行ずつ処理しながら所定のセルに値をセットすることもできます。
'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
コメント