Accessでテーブルからのデータ出力とエクセルのセルの背景色を設定する方法についての備忘録です。
実際に作ったファイルはこちらからダウンロードできます。
出力結果も載せているので、解説と合わせて見ていただければと思います。
エクセル参照設定
まずAccessファイルにエクセルを取り込むときに、エクセルを操作しなければなりません。
そのため、エクセル操作ができるよう参照設定を行います。
手順はコードエディターから「ツール(T)」→「参照設定(R)」と移動します。
その後、Microsoft Excel 16.0 Object Libraryを追加してください。
16.0はバージョンに寄ります。
エクセルの色付け 罫線引きサンプルコード
ボタンクリック後のメイン処理を記載します。
処理の流れは以下です。
①レコードセットにテーブルデータを取得
②エクセル出力
③色、罫線を付ける
④保存する
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
'============================================== '処理 ：Excel出力ボタン押下時 '============================================== Private Sub ExportExcel_Click() Dim db As DAO.Database 'DBの変数 Dim RS_Supplier As DAO.Recordset 'レコードセット Dim MyDir As String 'ツールが存在しているディレクトリ 'DBをセット Set db = CurrentDb '自分の配置されているパスを取得 MyDir = Application.CurrentProject.Path 'レコードセットへテーブルのデータを取得 Set RS_Supplier = db.OpenRecordset(GetSupplier()) '空エクセルファイルを開く Workbooks.Open (MyDir & "\nothing.xlsx") 'アラート表示をオフにする ActiveWorkbook.Application.DisplayAlerts = False '列名をエクセルに出力 Call SetRsColValue(ActiveSheet, RS_Supplier, 1, 1) 'レコードセットをエクセルに出力 Call SetRsValue(ActiveSheet, RS_Supplier, 2, 1) 'ヘッダーの色を付ける ActiveSheet.Range(Cells(1, 1), Cells(1, RS_Supplier.Fields.Count)).Interior.Color = RGB(204, 204, 204) '2～3列目の色を付ける ActiveSheet.Range(Cells(2, 2), Cells(RS_Supplier.RecordCount + 1, 3)).Interior.Color = RGB(221, 235, 247) '罫線を引く ActiveSheet.Range(Cells(1, 1), Cells(RS_Supplier.RecordCount + 1, RS_Supplier.Fields.Count)).Borders.LineStyle = xlContinuous '列幅を調節 ActiveSheet.Range(Cells(1, 1), Cells(RS_Supplier.RecordCount + 1, RS_Supplier.Fields.Count)).EntireColumn.AutoFit 'エクセル保存 ActiveWorkbook.SaveAs FileName:=MyDir & "\RS_Supplier.xlsx" 'エクセルを閉じる ActiveWorkbook.Close 'レコードセットを開放 Set RS_Supplier = Nothing MsgBox "出力完了しました。" End Sub |
解説
列名とテーブルデータをエクセルに出力する箇所は次で説明します。
ヘッダーの色を付ける
色付けする範囲をRangeで指定しています。
A1セル～A6セルまでに色を付けています。
範囲の開始位置はCells(1, 1)です。
範囲の終了位置はレコードセットの列数で指定しています。
なので、このロジックであれば列数が増えても汎用的に色付けできます。
2～3列目の色を付ける
こちらもRange指定でB2～C16までを色付けしています。
範囲の開始位置はCells(2, 2)でB2を指定しています。
範囲の終了位置はCells(RS_Supplier.RecordCount + 1, 3)でC16を指定しています。
RS_Supplier.RecordCount + 1で範囲の最終行と同じ数字になります。
レコードセットのデータは列含まないため+1しないといけません。
そのためテーブルデータの行数＋１をしています。
これもテーブルの行数が増えても汎用的に使えます。
罫線を引く
こちらもRange指定でA1～F16までを色付けしています。
Cells(1, 1), Cells(RS_Supplier.RecordCount + 1, RS_Supplier.Fields.Count)
RS_Supplier.RecordCount + 1で行数
RS_Supplier.Fields.Countで列数になるため、データがある範囲を対象にすることができます。
テーブルのエクセル出力
列名のエクセル出力
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
'============================================== '目的 ：エクセルのセルへ列名をセットする '引数 ：Worksheet 対象シート ' ：rs レコードセット ' ：StartRow 出力する行 ' ：StartCol 出力開始のカラム '戻り値 : 選択したファイルパス '============================================== Public Sub SetRsColValue(WS As WorkSheet, ByVal rs As DAO.Recordset, StartRow As Long, StartCol As Long) Dim ExcelCol As Long Dim RsCol As Long With WS ' 全列をシートに展開 For RsCol = 0 To rs.Fields.Count - 1 .Cells(StartRow, StartCol).Value = rs.Fields(RsCol).Name StartCol = StartCol + 1 Next RsCol End With Set rs = Nothing End Sub |
解説
エクセルの出力開始の行番号と列番号をStartRowとStartCol で引数で受取ります。
その後、列数分ループしながら列名を順番に出力する処理になっています。
RecordSetデータのエクセル出力
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
'============================================== '目的 ：エクセルのセルへ値をセットする '引数 ：Worksheet 対象シート ' ：rs レコードセット ' ：StartRow 出力する行 ' ：StartCol 出力開始のカラム '戻り値 : 選択したファイルパス '============================================== Public Sub SetRsValue(WS As WorkSheet, ByVal rs As DAO.Recordset, StartRow As Long, StartCol As Long) Dim Row As Long Dim ExcelCol As Long Dim RsCol As Long rs.MoveFirst With WS '出力開始行をセット Row = StartRow ' 先頭レコードからEOFまで繰り返す Do Until rs.EOF '列を先頭に戻す ExcelCol = StartCol ' 全列をシートに展開 For RsCol = 0 To rs.Fields.Count - 1 .Cells(Row, ExcelCol).Value = rs.Fields(RsCol).Value ExcelCol = ExcelCol + 1 Next RsCol '行を加算 Row = Row + 1 '次のレコードへ rs.MoveNext Loop End With Set rs = Nothing End Sub |
解説
列出力と同様に、エクセルの出力開始の行番号と列番号をStartRowとStartCol で引数で受取ります。
出力開始位置から1セルずつ、右に移動しながら出力していきます。
1ループ(1行分)終了すると次の行へ出力していきます。
出力結果
出力結果はこんな風になります。
・Web Safe Color (カラーチャート)
https://www.scollabo.com/banban/lectur/websafe.html
・参照渡しと値渡し
http://officetanaka.net/excel/vba/tips/tips94.htm
・セルの内容に合わせて列幅/高さを自動調整する
https://www.moug.net/tech/exvba/0050001.html
・【エクセルVBA】表の罫線を引く場所を指定する簡単な方法
https://tonari-it.com/excel-vba-borders/#toc2
■Excelマクロ
てっとり早く確実にマスターできる Excel VBAの教科書
■Accessマクロ
よくわかる Microsoft Access 2016 基礎
Accessのデータベースのツボとコツがわかる本2013対応
