SEの転職活動で使うべきサイト・エージェント 目指せ年収アップ
SEの転職活動で使うべきサイト・エージェント 目指せ年収アップ
東南アジアをバックパッカーしながら転職活動をした記録
東南アジアをバックパッカーしながら転職活動をした記録
7年目で初の転職活動 2カ月で内定獲得した記録
7年目で初の転職活動 2カ月で内定獲得した記録
おすすめのプログラミングスクール めざせ就職・年収アップ!
おすすめのプログラミングスクール めざせ就職・年収アップ!
メンズクリア2年通い放題入会! その後のヒゲ状況(不定期更新)
メンズクリア2年通い放題入会! その後のヒゲ状況(不定期更新)
ブログ開始3年が経った月収が1万超えてた!
ブログ開始3年が経った月収が1万超えてた!
プログラミング独学・スクール・就職(目次)
プログラミング独学・スクール・就職(目次)
previous arrowprevious arrow
next arrownext arrow
 
Shadow
VBA

VBA Access エクセル出力 色付け 罫線

Accessでテーブルからのデータ出力とエクセルのセルの背景色を設定する方法についての備忘録です。

実際に作ったファイルはこちらからダウンロードできます。

出力結果も載せているので、解説と合わせて見ていただければと思います。

エクセル参照設定

まずAccessファイルにエクセルを取り込むときに、エクセルを操作しなければなりません。

そのため、エクセル操作ができるよう参照設定を行います。

手順はコードエディターから「ツール(T)」→「参照設定(R)」と移動します。

その後、Microsoft Excel 16.0 Object Libraryを追加してください。

16.0はバージョンに寄ります。

エクセルの色付け 罫線引きサンプルコード

ボタンクリック後のメイン処理を記載します。

処理の流れは以下です。

①レコードセットにテーブルデータを取得
②エクセル出力
③色、罫線を付ける
④保存する

'==============================================
'処理    :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しないといけません。

そのためテーブルデータの行数+1をしています。

これもテーブルの行数が増えても汎用的に使えます。

罫線を引く

こちらもRange指定でA1~F16までを色付けしています。

Cells(1, 1), Cells(RS_Supplier.RecordCount + 1, RS_Supplier.Fields.Count)

RS_Supplier.RecordCount + 1で行数

RS_Supplier.Fields.Countで列数になるため、データがある範囲を対象にすることができます。

VBA C# オススメ参考書プログラミングの参考書でオススメの紹介をしていきます。 それぞれ私が使っている言語のみです。 VBAのオススメ参考書をExc...

テーブルのエクセル出力

列名のエクセル出力

'==============================================
'目的    :エクセルのセルへ列名をセットする
'引数    :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データのエクセル出力

'==============================================
'目的    :エクセルのセルへ値をセットする
'引数    :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行分)終了すると次の行へ出力していきます。

出力結果

出力結果はこんな風になります。


おすすめのプログラミングスクール めざせ就職・年収アップ! プログラミングを独学でされている人は意外と多いです。 ただ、プログラミングの独学は簡単にはいきません。 自分だけの力...
プログラミング初心者の独学方法 文系卒の経験談 私はIT系の会社に勤めて4年目ですが、最初の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

ABOUT ME
LooseCarrot
LooseCarrot
ブログ運営をしているLooseCarrotです。 興味のあることにトライして発信していきます! プロフィール

関連している記事