良く「時代遅れ」と言われるAccessやVBA・マクロなど。

でも実際には、一部上場の大企業も含めて非効率な作業が現場にたくさん転がっています。

特にExcelでの非効率作業の多いこと多いこと。

今回は、分かる方に向けたAccessからExcelへの出力プログラムVBAの書き方を紹介します。

中級者向けです。（たぶん）

AccessからExcel出力するには2つのパターンがある

AccessからExcel出力には、大きく分けて2つのパターンがあるんです。

まずは1行で済むパターン

DoCmd.TransferSpreadsheet acExport, 10, テーブル名１, エクセル名（フルパス）, True

恐らく、これで出力してる方多いと思います。

楽だし、これでも問題なく出せるんですがフォーマットに指定があったりすると応用がききません。

例えば、こんなの…

値の入るセルが3行目から始まっていますよね。（イラッとする）

1行で済むDoCmdのコードだと、1行目に項目がある状態じゃないと対応できません。

Excelのフォーマットが少し複雑になると、全く対応出来なくなるのでExcel関数で対応してる方も多いでしょう。

どんなExcelフォーマットでも怖いものなしなコード

AccessからExcel出力するVBAコードには、2つのパターンがあると紹介しました。

ここで紹介するもう1つのコードが、どんなフォーマットでも対応可能な怖いもの無しコードになります。

先ずはオブジェクトを宣言

Dim xlApp As Object'Excelアプリ'
Dim xlBook As Object'Excelブック'
Dim xlSheet As Object'Excelシート'
Dim rs As DAO.Recordset'レコードセット（テーブルでもクエリでもOK）'

次にオブジェクト系を変数へ格納していきます。

Dim xlApp As Object'Excelアプリ'
Dim xlBook As Object'Excelブック'
Dim xlSheet As Object'Excelシート'
Dim rs As DAO.Recordset'レコードセット（テーブルでもクエリでもOK）'

    Set xlApp = CreateObject(”Excel.Application”)
    Set xlBook = xlApp.Workbooks.Open(”[Excelファイルのフルパス]”）
    Set xlSheet = xlBook.Worksheets(”[Sheet名]”)
    Set rs = CurrentDb.OpenRecordset(”[テーブル名かクエリ名]”)

多分ここまでは、他の記事でもよく見ますよね。

でも。ここから普通にレコードを出力するサンプルコードが、なかなか見つかりません。

ここからは、レコードセットをエクセルのセルへ上手く出力するコード例を紹介していきます。

レコードセットの読み取りで「Do〜Loop」を使う

AccessからExcelへの出力は、つまりレコードセットの値をセルへ放り込む作業です。

レコードセットが出力元になるので、先ずはレコードセットを読み取る作業が必要ですよね。

レコードセットはExcel行みたいな見た目のAccess上にあるオブジェクト。

テーブルやクエリの結果がレコードセットになります。

一部例外があるものの、基本的にはレコードセットを開いた時は1行目を見ている状態。（カレントレコード）

なので、レコードセットを1番下の行まで読み取るにはカレントレコードを下げて行かないといけません。

カーソルを下げるイメージを持ってもらうと分かりやすいかも…

この読み取りを「Do〜Loop」で行います。

Do Until rs.EOF 'レコードセットがなくなるまで'
    処理
Loop　'繰り返す'

Untilは「そうなるまで」なので「rs.EOF」になるまで繰り返すと言うコードです。

EOFは「EndOfFile」の略で、レコードがない状態を表したもの。

つまり上記のコードは、レコードの存在が確認できなくなるまで繰り返すと言う意味になりますね。

じゃあ何を繰り返すのか？というと、一行のレコードセットを見てExcelのセルへ格納する行為が繰り返されるはずです。

Excelへの格納には「For〜Next」を使う

レコードセットを1つずつ読み取って、セルへ格納する部分には「For〜Next」を使います。

ちょっと書いてみましょう。

Const conRow = 3'格納する最初の行'
Const conCol = 1'格納する最初の列'

Dim iRow, iCol'行と列'

    iRow = conRow    
    For iCol = conCol to iFields'iFieldsはフィールドの数（※別途説明します）'
        xlSheet.Cells(iRow, iCol).Value=rs.Fields(iCol-1).Value
    Next iCol

Excelフォーマットには、色んなパターンが考えられますがリストには必ず値を格納する「最初の行と列」がありますよね。

ここをConstで表すのがポイントなんです。

Constで定数化しておけば、万が一フォーマットが変わっても修正が簡単。

「For〜Next」では、Excel列を移動させながら値を格納していくコードになるので、あくまで1レコードに対しての動きです。

全体的には「Do〜Loop」で1レコードずつの「For〜Next」を何度も繰り返す構図になります。

「Do〜Loop」の中で「For〜Next」を繰り返す

ここまでを踏まえて「Do〜Loop」の中で「For〜Next」を繰り返しながら、レコードセットからセルへ格納するコード例を記載します。

Dim xlApp As Object'Excelアプリ'
Dim xlBook As Object'Excelブック'
Dim xlSheet As Object'Excelシート'
Dim rs As DAO.Recordset'レコードセット（テーブルでもクエリでもOK）'
Dim iFields As Integer'フィールドの数'

Const conRow = 3'格納する最初の行'
Const conCol = 1'格納する最初の列'

Dim i'For用の変数'
Dim iRow, iCol'行と列'

    Set xlApp = CreateObject(”Excel.Application”)
        xlApp.Visible = True
    Set xlBook = xlApp.Workbooks.Open(”[Excelファイルのフルパス]”）
    Set xlSheet = xlBook.Worksheets(”[Sheet名]”)
    Set rs = CurrentDb.OpenRecordset(”[テーブル名かクエリ名]”)
    iRow = conRow'行の初期値'

        With rs
            iFields = .Fields.Count'レコードセットの列数を取得'
            Do Until .EOF
                For iCol = conCol To iFields
                    xlSheet.Cells(iRow, iCol).Value = .Fields(iCol - 1).Value
                Next iCol'次の列'                                                                                           
                .MoveNext'次のレコード'
                iRow = iRow + 1'次の行'
            Loop
            .Close
        End With

        xlBook.SaveAs ("[保存先のフルパス]")'名前を付けて保存'
        
        With xlApp
            .DisplayAlerts False'Excelの警告なし'
            .WorkBooks("[保存するファイル名]").Close'あくまでファイル名のみ'
            .Visible False
            .Quit
            .DisplayAlerts True
        End With

    Set rs = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing

前述で説明出来ていなかったのが「iFields」と言う変数。

Excelへ出力する際には、列数も把握しておかないといけません。

「rs!フィールド名.Value」の様に固定でも良いんですが、列数が多かったり可変的な列の場合に対応しきれません。

そこで事前にレコードの列数を変数へ代入して、同じ数だけのセル列で回していきます。

最後にいくつかの注意点を解説

ざっとサンプル見ていただいたところで、いくつかのバグポイントになる注意点を解説します。

まずは「.Fields」の部分。（※「With rs」で括っているので「rs.Fields」と言う意味になります）

  xlSheet.Cells(iRow, iCol).Value = .Fields(iCol - 1).Value

Fieldsの引数は0始まりなので「レコードセットの1列目=0」です。

だから必ず「−1」してあげましょう。

そして、もう1つがBookをSaveAsで保存した後の構文。

ここは、地味にバグ繰り返しました。

Excelへ出力する際には、指定のフォーマットを変えることなくリネームして新たなファイルを生成する方式にしたいところ。

となると当然SaveAsが良く使われてきます。

しかし新たに作ったファイルを閉じる時がじみーに厄介。

ThisWorkbooksとかActiveWorkBookとかありますが、筆者の記憶が正しければエラーになっちゃったこともありました。（※検証たらず）

実績として、1番確実な方法が「Application.Workbooks("[ファイル名]")」です。

.WorkBooks("[保存するファイル名]").Close'あくまでファイル名のみ'

前述のコードで表すと「xlApp.Workbooks("[ファイル名]").Close」

ここでのポイントは、フルパスじゃなくてファイル名のみを記載する点です。

Open時のようにフルパス記載しちゃうと、たちまちエラーになるので注意しましょう。

これでExcel出力職人だ！

たったの1行で出力できる機能は、もちろん素晴らしいものの普段の業務において応用の効かない部分があったと思います。

もしかすると、一旦自動で出力してから手作業が発生していた方もいるんじゃないでしょうか？

今回、紹介したコード例は実際に筆者が仕事で使っているものでありエラー発生率が極めて少ない書き方です。

これだ！と思った方は、是非一度試してみてくださいね。

DeepBlueでは、VBAに関する質問や要件定義の相談を受け付けています。

今回、この記事を読んで登録してくれた方には初回のみ無料でVBAマクロに関する相談を承りますよ。

興味ある方は、是非チェックしてみてくださいね。