Access VBAでエクセルをう。
最初に
Accessからエクセルを使用するには色々の方法が有りますがVBAを使用してエクセルに書式を指定して書き込む方法に限定して解説します。
尚、VBAを使用しますが、コピペで学んだ独学の為、あまり理屈はわかりません。
(ブラックボックス的に使っています。)
コードの記述も自分の分かりやすいように問題が無かった物は省略しています。
専門的に学習されたからは違うとのお叱りを受けるかもですが。
エクセルへの出力方法
ACCESSでエクセルを扱うときに参照設定で宣言する方法と宣言しない方法が有ります。
宣言した場合、エクセルのバージョンが変わるとエラーになります。
ですから私は全て参照設定しない方法でエクセルを扱っています。
（私の環境でもOffice2010/2013/2016/2019と色んなバージョンが使用されています）
参照設定する方法は事前バインド、しない方法を遅延バインドと言い、参照設定した方が動きが早いとの事ですが、100万件以上のデータを扱って初めてスピードの違いが明らかになる事らしいので参照設定しないエラーが出ない方法を選択しています。
(バインドの意味も理解していません）
エクセルを起動する
VBAを使用してエクセルを起動してみます。
Accessを起動し作成のフォームデザインを選択してコマンド釦を1個配置します。
コントロールウィザードの使用は選択しない。
コマンド釦を右クリック→イベントのビルド→コードビルダー選択→OK
VBE(Visual Basic Editor)画面が表示されます。
———————————————
Private Sub コマンド1_Click()
この間にコードを記述していきます。
End Sub
———————————————-
既存のBOOKを開くのでなく新規のBOOKを作成します。
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
以上を記述したらVBE画面を閉じフォームをフォームビューにしてコマンド1釦をクリックするとエクセルが起動し空白のbookが表示されます。
注意 エクセル参照設定しない場合の定数の指定は定数名でなく値を使用する。
（定数名を使うとエラーになる）
エクセルを使用する
文字の記述
セルのA1にテストと文字を入れる
xlBook.Worksheets(1).Range(“A1”).Value＝”テスト”
(.Valueは省略可↓）
xlBook.Worksheets(1).cells(1,1)＝”テスト”
セルA1からＤ1にテストと入れる
FOR I= 1 TO 4
xlBook.Worksheets(1).cells(I,1)＝”テスト”
NEXT I
FOR I= 1 TO 4
A=”A”& I
xlBook.Worksheets(1).Range(A)＝”テスト”
NEXT I
変数を使う場合はcellsの記述が便利です。
セルの書式設定（フォント)
フォント名
xlBook.Worksheets(1).cells(1,1).Font.Name=”ＭＳ 明朝”
フォントスタイル
太字 xlBook.Worksheets(1).cells(1,1).Font.Bold=True
斜字 xlBook.Worksheets(1).cells(1,1).Font.Italic = True
フォントサイズ
xlBook.Worksheets(1).cells(1,1).Font.Size=16
フォントカラー
xlBook.Worksheets(1).cells(1, 1).Font.Color = RGB(255, 0, 0)
カラーコードの調べ方
エクセルのフォントの色設定で取得する。
色設定で「その他の色」「ユーザー設定」で好きな色を選びカラーモデルはRGBを選択し赤緑青の数値を調べてRGB(赤、緑、青)で設定する。
他のカラー指定も有りますがこれが一番コードが分かりやすいと思います。
Withを使う事で複数同時に指定できる。
With xlBook.Worksheets(1).cells(1,1)
.Value=”ABC”
.Font.Name=”ＭＳ 明朝”
.Font.Bold=True
.Font.Size=16
.Font.Color = RGB(255, 0, 0)
End With
下線
xlBook.Worksheets(1).cells(1, 1).Font.Underline = 2
| 説明 | 値 | 定数名 |
| 無 | -4142 | xlUnderlineStyleNone |
| 一重下線 | ２ | xlUnderlineStyleSingle |
| 二重下線 | -4119 | xlUnderlineStyleDouble |
| 一重下線（会計） | 4 | xlUnderlineStyleSingleAccounting |
| 二重下線（会計） | 5 | xlUnderlineStyleDoubleAccounting |
取り消し線
xlBook.Worksheets(1).cells(1, 3).Font.Strikethrough = True
セルの書式設定（塗りつぶし)
カラー
xlBook.Worksheets(1).cells(1, 1).Interior.Color = RGB(255, 255, 0)
パターン
xlBook.Worksheets(1).cells(1, 1).Interior.Pattern = -4126
| 説明 | 値 | 定数名 |
| 塗りつぶし(網かけ無し) | 1 | xlSolid |
| 75%灰色 | -4126 | xlGray75 |
| 50%灰色 | -4125 | xlGray50 |
| 25%灰色 | -4124 | xlGray25 |
| 12.5%灰色 | 17 | xlGray16 |
| 6.25%灰色 | 18 | xlGray8 |
| 横縞 | -4128 | xlHorizontal |
| 縦縞 | -4166 | xlVertical |
| 右下がり縞 | -4121 | xlDown |
| 右上がり縞 | -4162 | xlUp |
| 斜線格子 | 9 | xlChecker |
| 極太斜線格子 | 10 | xlSemiGray75 |
| 横縞(広) | 11 | xlLightHorizontal |
| 縦縞(広) | 12 | xlLightVertical |
| 右下がり縞(広) | 13 | xlLightDown |
| 右上がり縞(広) | 14 | xlLightUp |
| 格子 | 15 | xlGrid |
| 斜線格子(薄) | 16 | xlCrissCross |
セルの書式設定（配置）
文字の位置（左右)
xlBook.Worksheets(1).cells(1,1).HorizontalAlignment = -4152
| 説明 | 値 | 定数名 |
| 標準 | １ | xlGeneral |
| 右揃え | -4152 | xlLeft |
| 中央揃え | -4108 | xlCenter |
| 左揃え | -4131 | xlRight |
| 繰り返し | 5 | xlFill |
| 両端揃え | -4130 | xlJustify |
| 選択範囲内で中央 | 7 | xlCenterAcrossSelection |
| 均等割り付け | -4117 | xlDistributed |
文字の位置（上下)
xlBook.Worksheets(1).cells(1,1).VerticalAlignment =-4160
| 説明 | 値 | 定数名 |
| 上詰め | -4160 | xlTop |
| 中央揃え | -4108 | xlCenter |
| 下詰め | -4107 | xlBottom |
| 繰り返し | 5 | xlFill |
| 両端揃え | -4130 | xlJustify |
| 均等割り付け | -4117 | xlDistributed |
折り返し全体表示
xlBook.Worksheets(1).cells(1, 1).WrapText = True
縮小して全体表示
xlBook.Worksheets(1).cells(1, 2).ShrinkToFit = True
セルを結合する
xlBook.Worksheets(1).RANGE(“A1:D1”).MergeCells = True
縦表示
xlBook.Worksheets(1).cells(1,1).Orientation = -4166
セルの書式設定（表示形式）
表示形式は代表的な物のみ示す。
数値
数値桁区切り
Withを使用しての記述
With xlBook.Worksheets(1).cells(1, 1)
.NumberFormatLocal = “#,##0”
.Value= 15000
End With
1行で記述
xlBook.Worksheets(1).cells(１, 1) = (Format(15000, “#,##0”))
この記述は数値に関して全てに使用できる
小数点以下桁数
xlBook.Worksheets(1).cells(1, 1).NumberFormatLocal = “#,##0.00_ ”
マイナス赤表記
xlBook.Worksheets(1).cells(1, 1).NumberFormatLocal = “#,##0.00_ ;[赤]-#,##0.00 ”
通貨
xlBook.Worksheets(1).cells(1, 1).NumberFormatLocal = “\#,##0_);(\#,##0)”
日付
xlBook.Worksheets(1).cells(1, 1)＝Format( Now, “yyyy/mm/dd ” )
xlBook.Worksheets(1).cells(1, 1)＝Format( Now, “yyyy年mm月dd日” )
| y | 年 |
| m | 月 |
| d | 日 |
時刻
xlBook.Worksheets(1).cells(1, 1)＝Format( Now, “hh:nn:ss” )
xlBook.Worksheets(1).cells(1, 1)＝Format(now , h時mm分ss秒” )
| ｈ | 時 |
| n | 分 |
| s | 秒 |
セルの幅
列
xlBook.Worksheets(1).Columns(“A:A”).ColumnWidth = 15
行
xlBook.Worksheets(1).Rows(“1:3”).RowHeight = 10
罫線
単線
下実線 xlBook.Worksheets(1).cells(1,1).Borders(9) .LineStyle = １
右下がり二重線：xlBook.Worksheets(1).cells(1, 1).Borders(5).LineStyle = -4119
Borders（線の位置）
| 上側 | 値 | 定数名 |
| 上側 | 8 | xlEdgeTop |
| 下側 | 9 | xlEdgeBottom |
| 左側 | 7 | xlEdgeLeft |
| 右側 | 10 | xlEdgeRight |
| 内側の縦線 | 12 | xlInsideHorizontal |
| 内側の横線 | 11 | xlInsideVertical |
| 斜線（右下がり） | 5 | xlDiagonalUp |
| 斜線（左下がり） | 6 | xlDiagonalDown |
LineStyle (線の種類）
| 説明 | 値 | 定数名 |
| なし | -4142 | xlLineStyleNone |
| 実践 | 1 | xlContinuous |
| 破線 | -4115 | xlDash |
| 一点鎖線 | 4 | xlDashDot |
| 二点鎖線 | 5 | xlDashDotDot |
| 点線 | -4118 | xlDot |
| 二重線 | -4119 | xlDouble |
| 斜め斜線 | 13 | xlSlantDashDot |
Weight (罫線の太さ)
| 説明 | 値 | 定数名 |
| 極細 | 1 | xlHairline |
| 細 | 2 | xlThin |
| 中 | -4138 | xlMedium |
| 太 | 4 | xlThick |
Box
セルA1～Ｈ10の場合
xlBook.Worksheets(1).Range(“A1:H10”).Borders.LineStyle = 1
セルで指定する場合（変数を使うときに便利）
xlBook.Worksheets(1).Range(xlBook.Worksheets(1).Cells(1,1),_
xlBook.Worksheets(1).Cells(10,8)).Borders.LineStyle =1
サンプル
表
| 区分 | 分類 | 数量 |
|---|---|---|
| A | AA | 15,000 |
| A | AB | 8,000 |
| A | AC | 12,000 |
| B | BA | 7,500 |
| C | CA | 24,000 |
| C | CB | 6,500 |
サンプルデータを下記のフォーマットでエクセルに書き込みます。
| 区分 | 分類 | 数量 | 合計 |
| A | AA | 15,000 | 35,000 |
| AB | 8,000 | ||
| AC | 12,000 | ||
| B | BA | 7,500 | 200 |
| C | CA | 24,000 | 30,500 |
| CB | 6,500 |
行の高さ 20 列の幅 9
サンプルVBA
この表を書くためだけのVBAです、実際はテーブルに有るデータを使用して表を作成するのでVBAの記述はもっと複雑になります。
Private Sub コマンド0_Click() Dim xlApp As Object Set xlApp = CreateObject(“Excel.Application”) DAT(1, 1) = “AA” :DAT(1, 2) = 15000 ’列幅 ‘行高さ ‘セル結合 ‘項目 ‘横方向中央揃え ‘データ書き込み ‘合計数値 ‘BOX罫線 End Sub |
最後に
VB /VBAと理屈は分からずに30年程実践で使っています。
とにかく何でも実践で出来る様なプログラムを書いてみることが一番だと思います。
他のoffice関連のブログも宜しければどうぞ
Acceeデータベースのテーブル名,フィルド名、データ型の一覧をエクセルに簡単に出力出来るフォーム
Access vba エクセルの列を選択してインポート出来るフォーム
Access vba ファルダ内ファイル名エクセル出力フォーム
MOS access2016模擬試験の繰り返し勉強のみで合格できた
