パソコン関連

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からD1にテストと入れる

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=”MS 明朝”

フォントスタイル

太字 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=”MS 明朝”
.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, 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 = 1

右下がり二重線: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~H10の場合

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
Dim xlBook As Object
Dim xlSheet As Object
Dim DAT(6, 2)

Set xlApp = CreateObject(“Excel.Application”)
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlApp.Visible = True

DAT(1, 1) = “AA” :DAT(1, 2) = 15000
DAT(2, 1) = “AB” :DAT(2, 2) = 8000
DAT(3, 1) = “AC” :DAT(3, 2) = 12000
DAT(4, 1) = “BA” :DAT(4, 2) = 7500
DAT(5, 1) = “CA” :DAT(5, 2) = 24000
DAT(6, 1) = “CB” :DAT(6, 2) = 6500

’列幅
xlBook.Worksheets(1).Range(“A:D”).ColumnWidth = 9

‘行高さ
xlBook.Worksheets(1).Rows(“1:7”).RowHeight = 15

‘セル結合
xlBook.Worksheets(1).Range(“A2:A4”).MERGE
xlBook.Worksheets(1).Range(“A6:A7”).MERGE
xlBook.Worksheets(1).Range(“D2:D4”).MERGE
xlBook.Worksheets(1).Range(“D6:D7”).MERGE

‘項目
xlBook.Worksheets(1).CELLS(1, 1) = “区分”
xlBook.Worksheets(1).CELLS(1, 2) = “分類”
xlBook.Worksheets(1).CELLS(1, 3) = “数量”
xlBook.Worksheets(1).CELLS(1, 4) = “合計”
xlBook.Worksheets(1).CELLS(2, 1) = “A”
xlBook.Worksheets(1).CELLS(5, 1) = “B”
xlBook.Worksheets(1).CELLS(6, 1) = “C”

‘横方向中央揃え
xlBook.Worksheets(1).Range(“A:B”).HorizontalAlignment = -4108
xlBook.Worksheets(1).CELLS(1, 4).HorizontalAlignment = -4108

‘データ書き込み
For I = 1 To 6
xlBook.Worksheets(1).CELLS(I + 1, 2) = DAT(I, 1)
xlBook.Worksheets(1).CELLS(I + 1, 3) = Format(DAT(I, 2), “#00,000#”)
Next I

‘合計数値
xlBook.Worksheets(1).CELLS(2, 4) = Format(DAT(1, 2) + DAT(2, 2) + DAT(3, 2), “#00,000#”)
xlBook.Worksheets(1).CELLS(5, 4) = Format(DAT(4, 2), “#00,000#”)
xlBook.Worksheets(1).CELLS(6, 4) = Format(DAT(5, 2) + DAT(6, 2), “#00,000#”)

‘BOX罫線
xlBook.Worksheets(1).Range(“A1:D7”).Borders.LineStyle = 1

End Sub

最後に

VB /VBAと理屈は分からずに30年程実践で使っています。

とにかく何でも実践で出来る様なプログラムを書いてみることが一番だと思います。