導入文
Access VBAを使うと、Accessに保存しているデータをExcelへ出力したり、書式付きの帳票を自動作成したりできます。
例えば、次のような作業を自動化できます。
- AccessのテーブルやクエリをExcelへ出力する
- 指定したセルへ値を書き込む
- フォント、色、罫線、表示形式を設定する
- Excelファイルを自動保存する
- 定型帳票をボタン1つで作成する
この記事では、Excelのバージョン差によるエラーが起きにくい遅延バインディングを中心に、Access VBAからExcelを操作する方法を解説します。
Access VBAからExcelを操作する2つの方法
AccessからExcelを操作する方法には、早期バインディングと遅延バインディングがあります。
早期バインディング
VBAエディターで「ツール」→「参照設定」を開き、次の項目にチェックを入れる方法です。
Microsoft Excel xx.x Object Library
宣言は次のようになります。
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
入力候補が表示され、Excelの定数もそのまま使えるため、コードを作る段階では便利です。
ただし、別のパソコンへ配布した場合、Officeのバージョンや参照設定の違いによってエラーが発生することがあります。
遅延バインディング
参照設定を行わず、すべてをObject型で宣言する方法です。
Dim xlBook As Object
Dim xlSheet As Object
Excelの起動にはCreateObjectを使います。
Set xlApp = CreateObject(“Excel.Application”)
入力候補が表示されず、Excel定数を数値で指定する必要がありますが、異なるOffice環境でも動作しやすいのが特徴です。
この記事では、配布や実務利用に向いている遅延バインディングで説明します。
Excelを起動して新しいブックを作成する
最初に、Excelを起動して新しいブックを作成します。
Private Sub Excel起動_Click()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Set xlApp = CreateObject(“Excel.Application”)
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlApp.Visible = True
End Sub
VisibleをTrueにすると、処理中のExcel画面が表示されます。
大量出力などで画面を表示する必要がない場合は、Falseにすると処理を高速化できます。
xlApp.Visible = False
既存のExcelファイルを開く
既存ファイルを操作する場合は、Workbooks.Openを使用します。
Set xlApp = CreateObject(“Excel.Application”)
Set xlBook = xlApp.Workbooks.Open(“C:\test\sample.xlsx”)
Set xlSheet = xlBook.Worksheets(1)
xlApp.Visible = True
ファイルが存在しない場合はエラーになるため、実務では事前にDir関数で確認すると安全です。
If Dir(“C:\test\sample.xlsx”) = “” Then
MsgBox “Excelファイルが見つかりません。”
Exit Sub
End If
Excelのセルへ値を書き込む
Rangeを使う方法
xlSheet.Range(“A1”).Value = “商品名”
xlSheet.Range(“B1”).Value = “価格”
Cellsを使う方法
xlSheet.Cells(1, 1).Value = “商品名”
xlSheet.Cells(1, 2).Value = “価格”
Cells(行番号, 列番号)で指定するため、ループ処理や変数を使う場合に便利です。
繰り返して書き込む
Dim i As Long
For i = 1 To 10
xlSheet.Cells(i, 1).Value = “データ” & i
Next i
With構文を使う
同じシートへ何度も書き込む場合は、With構文を使うと読みやすくなります。
With xlSheet
.Cells(1, 1).Value = “商品名”
.Cells(1, 2).Value = “数量”
.Cells(1, 3).Value = “金額”
End With
フォントやセルの色を設定する
フォント設定
With xlSheet.Range(“A1:C1”).Font
.Name = “メイリオ”
.Size = 12
.Bold = True
.Color = RGB(255, 255, 255)
End With
背景色を設定する
xlSheet.Range(“A1:C1”).Interior.Color = RGB(31, 78, 121)
見出し行に背景色と白文字を設定すると、見やすい帳票になります。
文字の配置を設定する
遅延バインディングでは、Excel定数の代わりに数値を使用します。
中央揃えは-4108です。
With xlSheet.Range(“A1:C1”)
.HorizontalAlignment = -4108
.VerticalAlignment = -4108
End With
折り返して表示する場合は次のようにします。
xlSheet.Range(“A1:C10”).WrapText = True
セル内に収まるよう縮小する場合は次の設定です。
xlSheet.Range(“A1:C10”).ShrinkToFit = True
セルを結合する
xlSheet.Range(“A1:D1”).Merge
xlSheet.Range(“A1”).Value = “売上一覧表”
xlSheet.Range(“A1”).HorizontalAlignment = -4108
セル結合を多用すると並べ替えやコピーがしにくくなるため、帳票のタイトルなど必要な場所だけに使うのがおすすめです。
数値・日付の表示形式を設定する
数値をカンマ区切りにする
xlSheet.Range(“C2:C100”).NumberFormatLocal = “#,##0”
小数点以下2桁を表示する
xlSheet.Range(“C2:C100”).NumberFormatLocal = “#,##0.00”
日付を表示する
値には日付データを入れ、表示形式を別に設定します。
xlSheet.Range(“A2”).Value = Date
xlSheet.Range(“A2”).NumberFormatLocal = “yyyy/mm/dd”
文字列として書き込むより、日付データとして保存した方がExcelで集計や並べ替えをしやすくなります。
列幅と行の高さを設定する
xlSheet.Columns(“A:A”).ColumnWidth = 15
xlSheet.Columns(“B:C”).ColumnWidth = 12
xlSheet.Rows(“1:3”).RowHeight = 22
内容に合わせて自動調整する場合はAutoFitを使います。
xlSheet.Columns(“A:C”).AutoFit
罫線を設定する
範囲全体に罫線を付ける場合は、次のコードで設定できます。
xlSheet.Range(“A1:C10”).Borders.LineStyle = 1
RangeとCellsを組み合わせると、行数が変動する表にも対応できます。
Dim lastRow As Long
lastRow = 10
xlSheet.Range( _
xlSheet.Cells(1, 1), _
xlSheet.Cells(lastRow, 3) _
).Borders.LineStyle = 1
Excelファイルを保存する
名前を付けて保存する場合はSaveAsを使います。
xlBook.SaveAs “C:\test\売上一覧.xlsx”
同名ファイルが存在すると確認画面が表示されるため、自動処理では警告を一時的に無効にできます。
xlApp.DisplayAlerts = False
xlBook.SaveAs “C:\test\売上一覧.xlsx”
xlApp.DisplayAlerts = True
保存先のフォルダーが存在しないとエラーになるため、事前に確認してください。
Excelを正しく終了する
Excelがバックグラウンドに残る主な原因は、終了処理やオブジェクトの解放漏れです。
xlBook.Close SaveChanges:=False
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
終了する順番は、子オブジェクトから親オブジェクトの順にします。
- Worksheet
- Workbook
- Excel.Application
エラーが起きてもExcelを終了するコード
実務では、途中でエラーが発生してもExcelを終了できるよう、エラー処理を入れておくのがおすすめです。
Private Sub Excel出力_Click()
On Error GoTo Err_Handler
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Set xlApp = CreateObject(“Excel.Application”)
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlApp.Visible = True
xlSheet.Range(“A1”).Value = “テスト”
xlBook.SaveAs “C:\test\sample.xlsx”
Exit_Handler:
On Error Resume Next
If Not xlBook Is Nothing Then
xlBook.Close SaveChanges:=False
End If
If Not xlApp Is Nothing Then
xlApp.Quit
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Exit Sub
Err_Handler:
MsgBox “エラーが発生しました。” & vbCrLf & _
Err.Number & vbCrLf & Err.Description
Resume Exit_Handler
End Sub
テーブルやクエリをExcelへ出力する
表形式のデータをそのまま出力するだけなら、TransferSpreadsheetが簡単です。
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=”T_売上”, _
FileName:=”C:\test\売上.xlsx”, _
HasFieldNames:=True
クエリを出力することもできます。
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel12Xml, _
“Q_売上集計”, _
“C:\test\売上集計.xlsx”, _
True
複雑な加工はAccessのクエリで済ませてからExcelへ出力すると、VBAコードを短くできます。
Recordsetを使ってExcelへ出力する
セル位置や書き込み内容を細かく制御したい場合は、Recordsetを使用します。
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rowNo As Long
Set db = CurrentDb
Set rs = db.OpenRecordset(“Q_売上集計”)
rowNo = 2
Do Until rs.EOF
xlSheet.Cells(rowNo, 1).Value = rs!売上日
xlSheet.Cells(rowNo, 2).Value = rs!担当者
xlSheet.Cells(rowNo, 3).Value = rs!金額
rowNo = rowNo + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
1行目には見出しを設定します。
With xlSheet
.Cells(1, 1).Value = “売上日”
.Cells(1, 2).Value = “担当者”
.Cells(1, 3).Value = “金額”
End With
Excel出力を高速化する
大量のセルへ書き込む場合は、画面更新や自動計算を一時停止すると高速化できます。
xlApp.ScreenUpdating = False
xlApp.EnableEvents = False
xlApp.Calculation = -4135
処理終了時には元へ戻します。
xlApp.Calculation = -4105
xlApp.EnableEvents = True
xlApp.ScreenUpdating = True
主な定数値は次のとおりです。
| Excel定数 | 数値 |
|---|---|
| xlCalculationManual | -4135 |
| xlCalculationAutomatic | -4105 |
| xlCenter | -4108 |
| xlContinuous | 1 |
AccessとExcelの連携方法の使い分け
| やりたいこと | おすすめの方法 |
|---|---|
| テーブルやクエリをそのまま出力 | TransferSpreadsheet |
| 書式付きの帳票を作成 | Excelオブジェクト操作 |
| データを加工してから出力 | クエリ＋TransferSpreadsheet |
| セル位置を細かく指定 | Recordset＋Cells |
| 複数シートを作成 | Excelオブジェクト操作 |
| 既存テンプレートへ書き込む | Workbooks.Open |
よくあるトラブルと原因
| 症状 | 主な原因 |
|---|---|
| Excelが終了後も残る | QuitやSet Nothingの不足 |
| 参照設定エラーになる | Officeバージョンや参照設定の違い |
| 保存できない | フォルダ不存在、権限不足、同名ファイル |
| セル指定でエラーになる | Worksheetを付けずにRangeやCellsを使用 |
| 動作が遅い | 1セルずつ大量に書き込み、画面更新が有効 |
| 別のPCで動かない | 早期バインディングの参照設定不足 |
特に、次のようにExcelオブジェクトを付けずにセルを指定すると、Excelが終了せず残る原因になることがあります。
Range(“A1”).Value = “テスト”
次のように、操作対象のシートを必ず付けてください。
xlSheet.Range(“A1”).Value = “テスト”
まとめ
Access VBAからExcelを操作するときは、まず次の基本を押さえましょう。
- 配布する処理では遅延バインディングを使う
- RangeやCellsには必ずWorksheetを付ける
- 単純な表出力はTransferSpreadsheetを使う
- 帳票作成はExcelオブジェクトを操作する
- 処理後はClose、Quit、Set Nothingを実行する
- 大量出力では画面更新と自動計算を停止する
AccessとExcelを連携できるようになると、データ入力、集計、帳票作成など、多くの業務を自動化できます。
最初はExcelの起動、セルへの書き込み、保存、終了という基本処理から始め、必要に応じて書式設定やRecordset出力へ広げていくのがおすすめです。
関連記事
- Access VBAでExcelへセル指定で出力する方法｜Range・Cellsの実践例
- Access VBA｜Excelの列を選択してインポートできるフォームの作り方
- Accessでフォルダ内のファイル名を一覧取得し、Excelへ出力する方法
- Excel VBA 定数検索ツール｜Access & PDFで定数をすぐ調べる方法
- Access VBAでよくあるトラブルと解決方法まとめ
