Tweet

Pocket

こんにちは！つむぎです。
さて今回は、「Accessの一覧フォームのデータを、サクッとExcelに出力しちゃおう！」っていうお話です。
たとえば、どんな場合かというと、下のフォームのような場合ですね♪

では、早速いってみましょう！

①まず、AccessのVBAを記載する画面であるVBEエディタを開いてください。
参考までに、Accessを起動している場合は、[Alt]キー+[F11]キーを同時に押すとVBEエディタが起動します。（Windowsの場合）
※その他の方法でのVBEエディタの開き方は、ご自身で調べてくださいね。

VBEエディタが開いたら、標準モジュール内に下記のように入力してください。
わたしは、「関数」という名前の標準モジュール内に入力しました。

このソースで、Excelにデータを出力するための関数が作成されます。
ソースコードは、下記を参考にしてくださいね。

'Excelにデータを出力
Function ExcelData(frm As Form)
    On Error GoTo Err_cmdExcel_Click

    'DAOで抽出結果のクローンを作成
    Dim xls As Object 'Excel.Applicationを代入するオブジェクト変数
    Dim wkb As Object 'Excel.Wookbookを代入するオブジェクト変数
    Dim rst As DAO.Recordset '現在のレコードセットを入れる変数
    Dim idx As Long  'フィールド数変数

    Set rst = Nothing 'データリストの初期化
    Set rst = frm.RecordsetClone  'フォームのレコードセットのクローンを代入

    'レコードが存在しない場合、処理を中止
    If rst.BOF = True And rst.EOF = True Then
        MsgBox "出力出来るデータがありません。", vbOKOnly + vbExclamation, "出力不可"
        'レコードセットを閉じる
        rst.Close: Set rst = Nothing
        Exit Function
    End If

    'レコードが存在する場合、Excelに出力
    'レコードセットの最初のデータにカーソルを移動
    rst.MoveFirst

    'Excelファイルを内部的に作成
    Set xls = CreateObject("Excel.Application")
    '作成されたExcelファイルにワークブックを追加
    Set wkb = xls.Workbooks.Add()

    '追加されたワークブックに、レコードセットのデータをコピー
    With wkb.Worksheets(1)
        For idx = 1 To rst.Fields.Count
            .Cells(1, idx).Value = rst.Fields(idx - 1).Name
        Next
        .Range("A2").CopyFromRecordset Data:=rst
    End With

    'レコードセットを閉じる
    rst.Close: Set rst = Nothing
    'Excelデータを表示
    xls.Visible = True
    xls.UserControl = True
    'メモリに展開されたExcel用オブジェクト変数を開放
    Set wkb = Nothing
    Set xls = Nothing

Exit_cmdExcel_Click:
    Exit Function

Err_cmdExcel_Click:
    'エラーの場合、エラーNOと内容を表示
'    MsgBox Err.Number & Err.Description
    MsgBox "エラーのため、Excelへ出力できません。" & vbCrLf & "一旦フォームを閉じ、再度トライしてください。", _
    vbOKOnly + vbCritical, "Excel出力不可！"
    Resume Exit_cmdExcel_Click
End Function

②次にフォームが持っているイベントから、上で作った「ExcelData」関数を呼び出します。
フォームのイベントの中から、「cmdExcel」の「クリック時」に呼び出し用のプログラムを設定しました。

設定したプログラムの内容は、下記になります。

'一覧データをExcelに出力
Private Sub cmdExcel_Click()
    'Meは、このフォームという意味
    Call ExcelData(Me)

    '（forms!Form商品一覧というように、ここにフォーム名を指定してもOK!）
    'Call ExcelData(Forms!商品一覧)
End Sub

で、出力されたExcelは？というと、下記のような感じで出力されました。

この関数が便利なところは、抽出したデータでも一覧データでも、Excelにサクッと出力してくれるところ。
「Book1」という形で出力されるので、自分で好きな名前をつけて保存してもいいし、保存しなくてもOKっていうのも便利！
わたしは、AccessのフォームからExcelにデータを出力する時、もっぱらこの方法を使ってます。
AccessのフォームからExcelにデータを出力する時は、ぜひ、お試しくださいね♪

Tweet

Pocket