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をおこない行を変更しています。

エクセルへテーブルのデータがコピーされていれば成功です。

いかがでしょうか？
Excelへの変換はもっと単純な方法がありますが個人的に好きな方法ですので記事にしました。
You Tubeにも動画を公開していますので実際の動きを確認することが出来ます。