汎用的なツールで作るDB→Excel→メール配信の自動システム：Windows環境でちょっとした定型業務を自動化する（1/2 ページ）

連載目次

　社内のデータベース・サーバから定期的に必要なデータを抽出し、レポートとして配布する、というのはよくある業務だ。レポートの体裁を整えたり配布先のメール・アドレスを設定したり、といった作業は定型であり、しかも手作業だと意外に時間を食われるので、ぜひ自動化したいところだ。

　ただ、自動化といってもその手法はさまざまだ。できることなら、多くの業務に応用しやすい「つぶしの効く」手法を身に付けるのが望ましい。そこで本稿では、なるべく汎用的で応用しやすいツールを活用するという方針で、筆者が実際に作った社内データベースからの自動レポート作成・配信システムの一例を紹介したい。WindowsクライアントPC上で実行することを前提に、バッチ・ファイル主体で組み上げたものだ。正直にいって実装には拙いところもあるし、高度なスクリプト言語に精通していればもっとスマートでメンテナンスのしやすい実装もあるだろう（その点で、本稿の実装がベストだなどと主張するつもりはまったくない）。一方で、あまりスクリプトに慣れていないIT Proには利用しやすいテクニックがあると思う。さまざまな処理の自動化の参考にしていただければ幸いだ。

　なお、DBMSとしてはSQL Serverを対象としている。またデータベースからデータを抽出するというテーマ上、ある程度のT-SQLの知識が必要となることはご了承いただきたい。

例として取り上げるレポート配信システム

　本稿で実例として取り上げるのは、弊社の「補助金サーチ・メール・サービス」の自動配信システムである。このサービスでは、住宅向け太陽光発電設備を設置する際に自治体から提供される補助金の金額や提供条件などを、Excelブックに取りまとめてレポート化し、毎月1回、メールに添付して顧客に配信している。

「補助金サーチ・メール・サービス」で配布しているExcelシート
閲覧しやすいようにセルや文字の色、罫線などの装飾を施している。右端のリンクをクリックするとダイレクトに各自治体の補助金情報ページを参照できる。詳細は後で説明するが、いずれも自動で設定している。

　補助金情報はSQL Serverによるデータベースに集約しているので、そこから必要な情報を抽出してExcelブックに書き込み、体裁を整えてからメールに添付して、顧客に1通ずつ配信する、という仕組みだ。以下に各処理を分類する。

1. SQL Serverから必要なデータを抽出
2. 1．のデータをテキストとして整形
3. Excelに2．のデータを入力してExcelブックに変換
4. 3．のExcelブックをメールに添付して各顧客に送信

どうやってSQLデータベースからデータを抽出するか？

　SQL Serverのデータベースからデータを抽出するには多くの方法がある。例えばExcelから直接SQL Serverに接続して抽出することも可能だ。前述の処理内容からすると、これがスマートな方法に感じられる。実際に筆者も最初はこの方法で実装しようとしたが、VBA（Visual Basic for Application）のデータベース関連機能を使いこなせず、SQL Serverに問い合わせる（クエリをかける）たびに異なる結果が返るなど、思ったようにデータを抽出することができなかった。

　そこで、代わりにSQL Serverへクエリをかけて戻り値を標準出力に出力するSQLCMDコマンドを利用した。これはSQL Serverに付属していて、SQL Serverのクライアント・ツールをインストールすると利用できる（データベース・エンジンをインストールできないクライアントPCでも利用可能）。SQLCMDを使うと、Excelへのインポート処理が必要になる半面、出力がExcelブック以外の場合にも応用できるというメリットがある。

　SQLCMDコマンドでデータベースからデータを抽出するコマンドラインの一例を記す。ここではWindowsにログオン中のユーザー・アカウントが持つ資格情報でSQL Serverにアクセスし、カンマ区切りのCSV形式でデータを出力し、「subsidy.csv」というファイルに保存している。

　各オプションの意味は次の通りだ。

　SQL Serverに保存されたアカウントでアクセスする場合には、-Eオプションの代わりに-Uオプションと-Pオプションを指定する必要がある。SQL Serverの設定や環境に応じてどちらかを選べばよい。SQLCMDコマンドの詳細については次のTechNetのページを参照していただきたい。

• SQLCMDコマンド（マイクロソフト TechNet）

　前述のコマンドラインにある「make.sql」すなわちT-SQLファイルには、あらかじめデータベースから必要なデータを抽出するためのT-SQL文を記述しておく。以下はその一例である（この内容はテーブルの内容や必要な出力結果に大きく依存するので、細かい説明は省かせていただく）。

データベースから抽出されたテキスト・データをどうやって整形するか？

　データベースからデータを抽出したら、Excelブック化する前に、不要な行を削除したり日付の形式を変換したり、といったテキスト整形をする。ExcelでもVBA（Excelマクロ）の文字列操作機能によって自動整形は可能だが、そのテクニックはOffice以外のツールには応用できない。その点では、テキストのまま処理できる汎用的なツールの方が便利といえよう。

　本稿の場合、テキスト・エディタの「秀丸エディタ」が備えるマクロ機能でテキスト整形を自動化している。秀丸エディタは有償のシェアウェアで、次のページからダウンロードして試用できる。

• 秀丸エディタ（サイトー企画）

　マクロなどによる自動処理と正規表現によるテキスト検索／置換、コマンドラインから自動実行ができるなら、秀丸エディタ以外のテキスト・エディタ、あるいはPerlなどでも実現できるだろう。

　さて、秀丸エディタのマクロで自動処理を行うには、次のようなコマンドラインを実行する。

　秀丸エディタの実行ファイル「Hidemaru.exe」が起動されると、まず「CSV\subsidy.csv」が処理対象ファイルとしてオープンされ、その次に「MakeSubSidySheet.mac」に記載したマクロが自動的に実行される。各オプションの意味は次の通りだ。

　マクロの内容も記しておく。実は正規表現によるテキスト置換しかしていない単純なものだ。

どうやってExcelブックの体裁整形を自動化するか？

　CSVファイルのテキスト整形を終えたら、Excelに読み込ませてExcelブックを作成し、罫線や文字色などの装飾をしたり、セルの列幅を揃えたりしながら体裁を整える。これを自動化するにはVBAを利用するが、一からVBAを記述するのは手間が掛かるので、Excelのマクロ記録（いわゆるキーボード・マクロ）で体裁を整える操作をVBAに変換してから、必要に応じて編集・修正するのが簡単だ。

●マクロを記録する

　体裁の整え方を決めたら、対象のCSVファイルを開いた状態でリボンの［表示］タブ−［マクロ］の▼マークをクリックし、メニューから［マクロの記録］をクリックする。

Excelでキーボード・マクロを記録する手順
手動で操作した結果を「キーボード・マクロ」として記録し、体裁を整える手順をマクロ化する。
　 （1）このタブを選ぶ。
　 （2）この▼マークをクリックしてメニューを表示させる。
　 （3）これをクリックするとマクロ記録のダイアログが表示されるので、マクロ名を指定して［OK］ボタンをクリックする。これでマクロの記録が始まる。

　表示されたダイアログでマクロ名を指定し、マクロの保存先に「新しいブック」を選んで［OK］ボタンをクリックする。「新しいブック」にマクロを保存するのは、他のユーザー・アカウントで自動実行する際に扱いやすいからだ（個人用マクロブックは記録時のログオン・アカウントからしか利用できない）。

　これでマクロの記録が始まるので、体裁を整える操作をしていく。そのときのポイントの1つは、データ範囲内でセルのフォーカスを移動する際は［Ctrl］＋矢印キー、範囲指定の際は［Ctrl］＋［Shift］＋矢印キーをそれぞれ使うことだ。これにより絶対座標（"B2"とか）でセルを参照するマクロが減るため、後で行うマクロの編集作業が少なくなる。

　操作を終えたら、［表示］タブ−［マクロ］の▼マーク−［マクロの記録終了］をクリックする。また、マクロを保存した新規ブックは、マクロ有効ブック（*.xlsm）フォーマットで名前を付けて保存する。