【Access × Excel連携】VBAでデータをExcelに出力する方法
「Accessのデータをもとに書類を作りたいけど、毎回手作業で作成するのは面倒…」
そんな経験はありませんか？
Access VBAを使えば、ボタン一つでテーブルのデータをExcelに自動出力することができます。
この記事ではAccess × Excel連携でVBAを使いテーブルデータを簡単にExcelに出力する方法を解説します。
- AccessデータをExcelに自動出力する方法
- Excelテンプレートでの書類作成方法
- 保存先・ファイル名の設定方法
- VBAでのExcel操作と注意点
AccessからExcelを操作するVBA
Access VBAを使うとAccessのデータをExcelへ自動で転記することができます。
まずは基本的なVBAコードを確認してみましょう。
基本的なコード
Sub ExportExcel()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim path As String
path = "C:\Temp\template.xlsx"
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("テーブル名")
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(path)
Set xlSheet = xlBook.Sheets("シート名")
' データ書き込み
Dim rowNum As Long
rowNum = 2 ' 1行目は見出し
Do Until rs.EOF
xlSheet.Cells(rowNum, 1).Value = rs!社員名
xlSheet.Cells(rowNum, 2).Value = rs!部署名
rowNum = rowNum + 1
rs.MoveNext
Loop
xlApp.Visible = True
rs.Close: Set rs = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Subコードの内容解説
サンプルコードが行っている内容は次の通りです。
- Excelテンプレートのパスを指定
- Accessテーブルのデータを取得
- Excelオブジェクトを使ってExcelを起動
- 取得したデータをExcelに書き込む
注意
このコードではテンプレートに直接書き込むため、保存すると元ファイルが上書きされます。
新しいファイルとして出力する場合はSaveAsを使って保存してください。
詳しい手順は次の見出しをご参照ください。
Excelデータの保存先と保存名を指定して保存する
Access VBAでは作成したExcelデータを任意の保存先と保存名で保存することができます。
基本構文
Dim savePath As String
savePath = "C:\Temp\出力ファイル.xlsx"
xlBook.SaveAs savePathExcel.Workbook型の変数のSaveAsメソッドを使うことで、
保存先と保存名を指定してExcelファイルを保存できます。
この書き方のメリット
SaveAsメソッドを使うことで次のようなメリットがあります。
- ファイル名をVBAで自由に指定できる
- 保存先フォルダを自由に指定できる
- 既存ファイルがあれば上書き保存も可能
ファイル名を動的に切り替える
テーブルデータなどの内容によってExcelファイルの名前を動的に切り替えることも可能です。
Dim savePath As String
savePath = "C:\Temp\" & rs!社員名 & "_" & Format(Now, "yyyymmdd") & ".xlsx"
xlBook.SaveAs savePathFormat(Now, "yyyymmdd")で日付をファイル名に組み込める- テーブルの値を組み合わせて動的にファイル名を生成できるため、帳票作成に便利
実務で使える応用例
実際にAccessのフォームで選択したレコードの情報をもとに、Excelテンプレートを使って領収書を作成するVBAサンプルをご紹介します。
サンプルコード
Sub CreateReceipt()
Dim xlApp As Object, xlBook As Object, xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open("C:\Temp\ReceiptTemplate.xlsx")
Set xlSheet = xlBook.Sheets("領収書")
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If Me.NewRecord Then MsgBox "レコードを選択してください": Exit Sub
rs.Bookmark = Me.Bookmark
xlSheet.Range("B3").Value = rs!顧客名
xlSheet.Range("B4").Value = rs!日付
xlSheet.Range("B5").Value = rs!金額
xlBook.SaveAs "C:\Temp\領収書_" & rs!顧客名 & "_" & Format(Now, "yyyymmdd") & ".xlsx"
MsgBox "領収書を作成しました。", vbInformation
rs.Close: Set rs = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Subポイント解説
このサンプルコードで行っている処理は次の通りです。
- フォームで選択中のレコードを取得
- Excelテンプレートを開き、指定セルに値をセット
SaveAsで元テンプレートを上書きせず別名で保存- 作業後にExcelオブジェクトを
Nothingで解放
これによりテーブルのデータから顧客名＋日時を組み合わせた領収書ファイルを自動生成できます。
Access × Excel連携の注意点
Excelファイルのパスを確認する
Excelファイルのパスが間違っているとファイルを開く際にエラーになります。
Dim path As String
path = "C:\Temp\template.xlsx"
Set xlBook = xlApp.Workbooks.Open(path)パスは絶対パスで指定するようにしましょう。
長いパスや複数の個所で使用する場合は変数に入れることで管理が楽になります。
Excelオブジェクトを開放する
Access VBAでExcelを操作したら変数をNothingに設定してオブジェクトを解放しましょう。
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing省略しても動作しますがExcelが裏で残ったりメモリを消費するのを防ぐため付けておくのがおすすめです。
まとめ
今回はAccess × Excel連携で、VBAを使ってテーブルデータをExcelに出力する方法を解説しました。
- 決まったフォーマットで書類を自動作成できる
- 保存先やファイル名を自由に設定できる
- 手作業で行っていた作業をボタン一つで自動化し業務効率化につながる
この方法を使えば、毎月の帳票作成や領収書発行にかかる時間を大幅に短縮できます。
コメント