AccessでExcelを操作する

AccessはExcelと比較した場合、データの加工(クエリや大量のデータの処理、レポート機能)という点で、Excelよりも優れています。データ処理については速度面でも非常に有利です。一方でExcelの長所がそのままAccessの弱点になっています。

例えば縦横柔軟な表形式の文書の作成であったり、機能面での差として「クロス集計」に於いて、Excelでは出来てもAccessではできないものが存在していたりします(Accessでは列の要素は1個しか指定が出来ない)。また、クエリでは現場の要望の一部しか満たせない場合もあります(クエリ使うよりも、sumiufsのほうが手っ取り早かったり)

そんなAccessですが、Excelを遠隔操作出来たりするので、流れ作業でExcelを操作してデータの出力が非常に便利です。今回はこの遠隔操作である「オートメーション」を見ていきたいと思っています。

今回使用するファイル

事前準備

参照設定の追加

今回のAccessアプリは普段使っているDAOではなく、ADOを用いてレコードデータを取得しています。そこで以下の手順で参照設定を追加しておかないとコードが動きません。また、今回もリボンを使ってるので、その為の参照設定も追加しておきます。

  1. データベースツールタブの「Visual Basic」を開きます。
  2. メニューより「ツール」⇒「参照設定」を開きます。
  3. Microsoft ActiveX Data Objects 2.8 Library」を見つけ出しチェックを入れます。
  4. リボンの為に「Microsoft Office 15.0 Object Library」を見つけ出しチェックを入れます。

この設定を加える事で、ADOでCopyFromRecordsetを利用する事が出来るようになります。

図:ADOもいろいろバージョンがあるけれど

データの整備

今回のサンプルデータは、国土地理院の国土数値情報で配布されているGIS用のデータを加工して、Accessに取り込んであります。DBFファイルが本体で、LibreOfficeにて取り込み、CSVに変換した後、Accessへとインポート済みです。さらに書き込み用に「緯度と経度」の列をカンマで連結させた列を追加したクエリを用意してあります。

実際の書き込みでは、このクエリを出力先のExcelワークシートへと書き込みすることになります。

図:書き込み用に列を追加してある

ソースコード

レコードセットをそっくり出力

  • 起動すると、名前を付けて保存ダイアログが開き、ファイル名入力を促されます。
  • テンプレートになるroadstation.xlsxがコピーされ開き、そこへCopyFromRecordsetにて一発書き込みをしています。
  • 書き込み元は「出力用クエリ」で、緯度経度を連結させた列を含めて必要なデータを書き込みしています。
  • 書き込み中は、再計算やアプリケーションの表示はさせない用に制御しているので、高速で書き込みが終わります。
  • デスクトップフォルダのパスの取得や、Excel本体については、実行時バインディングにて処理をしているので、「参照設定」への追加は不要です。
  • DAOであっても、CopyFromRecordsetは利用が可能です。
  • また、CopyFromRecordsetの場合書き出し可能なデータ量に制限があるようです。。

レコードをフィルタして出力

前項の内容はクエリやテーブルの中身をそのまま出力する方法です。しかし、時には同じクエリやテーブルに対して動的にフィルタ(WHERE条件)を付けて、連続で出力したい場合があります。大抵このケースでは、テンプレートになるExcelに対してフィルタの内容に応じてシートを追加し、出力していくケースが多いです。メソッド自体は同じ内容を利用します。

  • template.xlsxがstrFileNameに入っています
  • template.xlsxはシートコピー元のtemplateという名のシートがあります。
  • 分類一覧には分類項目が入っており、この数だけシートを複製しその分類を元に出力用クエリをフィルタして出力するのが目的です。
  • ADOのExecuteでSQL文を実行し、出力用のクエリをWHERE条件付きでフィルタしてrecに格納しています。
  • Copy After:=.Worksheets(.Worksheets.Count)にて、一番うしろにtemplateシートのコピーを作ります。
  • コピーしたシートをアクティブにし、そこへrecの内容をCopyFromRecordsetで出力します。これを分類数分繰り返す
  • 最後にtemplateシートを削除して保存し完了です。

Excelのメソッドを使って1行ずつ書き込み

前項ではCopyFromRecordsetにてクエリのデータを所定のシートに対して、データを一括書き込みを行いました。しかし、CopyFromRecordsetの場合、クエリやテーブルをそのまま書き込むので、データの加工はVBA上ではなくクエリ上で完成させておく必要性があります。

一方で、特定の項目の内容に応じて条件判定を行いデータをいろいろ加工したり追加したいようなケースの場合、事前にクエリやテーブルで対処しておくのは難しい事です。そこで、テーブルのデータの書き込みを通常のExcel VBAを扱うように出来たら便利だなという事で以下のようなコードもサンプルに加えてあります。

  • 途中までは前項とほぼ同じルーチンになります。
  • 今回のルーチンはDAOで作業をしているので、このコード自体は追加の参照設定は必要ありません。
  • こちらは1行ずつワークシートに書き込む操作なので、CopyFromRecordsetより速度面では非常に遅いです(配列で処理をしたら早くなるとは思います)
  • Excel VBA用のメソッドもAccess VBA上で利用が可能になるので、利便性は向上します。
  • 複雑な条件判定などを事前にクエリ側で作るよりも、プロシージャ側で作り込める為、その後のメンテナンスなどを考えた場合、こちらのやり方のほうがメンテナンス性では有利だと思います。
  • 単純にデータの書き込みだけでなく、グラフの作成やピボットテーブルの作成、またはそれらの操作などもAccessからExcelのワークシートへ行えるので、この手法は手法で身につけて置くとあとで助かります。
  • 現在のAccessはExcelにリンクテーブルで接続しても書き込みは出来ません(昔はできたんですけれどねぇ)

関連リンク

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)