Access VBA AccessのデータをExcel（エクセル）へ変換

AccessのデータをExcel（エクセル）へ変換

Accessで使用しているデータをExcel（エクセル）へ変換する方法を説明します。今回説明する方法は個人的に好きなものでAccess上からエクセルを開いてデータが入力されていく様子が分かる変換方法です。

Excelを使用する為の参照設定を行う

今回の方法はAccess上からExcelを開き操作します。
その為にExcelを使用する為の参照設定を行います。

参照設定から「Microsoft Excel ** Object Library」を選択します。
これでAccess上でExcelの操作が行えます。

テーブルとフォームの準備

・テーブルの準備
Excelに変換するデータを入れるテーブルを作成します。
どんなテーブルでも構いませんので今回はフィールドとしてData1～Data10を作成し、それぞれのフィールドに1～100までの値を入れておきます。また、テーブル名は「T_エクセル変換」とします。

・フォームの準備
フォームの作成も単純です。コマンドボタンをひとつ設置しただけです。
後々、コマンドボタンのクリック時イベントにExcel変換のプログラムを書き込みます。

Excelファイルの準備

変換用のExcelファイルを事前に用意しておきます。

先ほど作成したAccessテーブルのフィールド名と同じ、Data1～Data10をあらかじめ入力しておきます。Sheet名を「変換シート」としファイル名を「エクセル変換.xls」とし任意のフォルダに保存しておきます。
この作成したファイルをAccessから呼び出し操作することになります。

AccessのデータをExcel（エクセル）へ変換するプログラムコード

ここからはいよいよExcel変換のプログラムを説明します。
プログラムを記述する場所は先ほど作成したフォーム内に設置したコマンドボタンのクリック時イベント内です。

Private Sub コマンド0_Click()

Dim xls As Object
Dim wkb As Object
Dim r As Variant
Dim filename As String
Dim i As Integer
Dim k As Integer

filename = “C:My Documents\エクセル変換.xls”

Set xls = CreateObject(“Excel.Application”)
Set wkb = xls.Workbooks.Open(filename, , True) ‘第3引数 true = 読取専用

‘Excel表示
wkb.Application.Visible = True
wkb.Activate

‘シート選択
wkb.Worksheets(“変換シート”).Activate
wkb.Worksheets(“変換シート”).Cells(2, 1).Activate

Dim CN As New ADODB.Connection
Dim RS As New ADODB.Recordset

Set CN = CurrentProject.Connection
RS.Open “T_エクセル変換”, CN, adOpenStatic, adLockReadOnly

i = 2
Do Until RS.EOF

For k = 1 To RS.Fields.Count
wkb.Worksheets(“変換シート”).Cells(i, k) = RS.Fields(k – 1).Value

Next
i = i + 1
RS.MoveNext
Loop

RS.Close: Set RS = Nothing
CN.Close: Set CN = Nothing

End Sub

プログラムの説明

filename = “C:My Documents\エクセル変換.xls”
変数「filename」に作成したExcelファイルのアドレスとファイル名を格納します。

Set xls = CreateObject(“Excel.Application”)
Set wkb = xls.Workbooks.Open(filename, , True) ‘第3引数 true = 読取専用
Excelファイルを読み取り専用で開きます。

wkb.Application.Visible = True
wkb.Activate
Excelを表示します。

wkb.Worksheets(“変換シート”).Activate
wkb.Worksheets(“変換シート”).Cells(2, 1).Activate
Sheetを指定し、あらかじめ選択しておくセルも指定します。

Set CN = CurrentProject.Connection
RS.Open “T_エクセル変換”, CN, adOpenStatic, adLockReadOnly
変換するAccessテーブルを開きます。
ここはSQL文で実行しても構いません。

i = 2
Do Until RS.EOF

For k = 1 To RS.Fields.Count
wkb.Worksheets(“変換シート”).Cells(i, k) = RS.Fields(k – 1).Value
Next
i = i + 1
RS.MoveNext
Loop
For～Nextを使用して、テーブルの値をExcelのセルへ入れていきます。
変数kはフィールド数（今回の場合は10列）を指定し、フィールドは0から数えますので値を指定する場合はk-1としています。また、Excelの列番号も兼ねています。
変数iはExcelの行番号で1行分のデータ変換が終わる度にi+1をおこない行を変更しています。