お久しぶりでございます。scalaでバックエンドを開発しているaxtstar(@axtstart)でございます。
前回はRe:ゼロから始めるJavaScript入門
の話をしましたが、今回は、、、、
やっとScalaの話…ではなく、Scalaは私よりも詳しい方にお任せして
Excel マクロでツールを作りましたのでその話をします。
DevOpsでよくある要望
DevOpsや開発、システムテスト等で、とあるExcel上にあるデータをDBに格納したい。みたいな案件・要望はよくあると思います。
そしてそのExcelファイルも、セルのデータの中に改行コードや引用符がないのであれば良いのですが、そうとも限らないというのが実情です。 また昨今はxmlやJSONといった形式のデータがセルに格納されていて、それを登録するなんて話もあったりするかもしれません。
なのでVBAでExcelのセル内容をそのままDBに格納するExcelマクロを作りました。都合上PostgreSQL専用になっています。
.NETのCOM相互運用機能やJavaのpoiを使って、Excelファイルを読み込むことで、同様の機能は作れますが、プログラムのメンテナンス性を除いては、 データと処理が一体化できるExcelマクロに優位性があると感じて作成してみました。 *1
ただVBAのプログラムのメンテナンス性は著しく悪いです。
ソースファイルとExcelファイルが一体となって、バイナリで出来ている（最近のExcelの場合、xmlをzip圧縮しています。）
VBA自体の仕様が古いまま、あまり更新されていない
そこは少し調査して下記により、ソース（VBA）とバイナリ（xlsmファイル）を分離することにしました。
事前準備
残念ながら、Excelマクロは既定の状態では実行できない *2 ように設定されているため、下記によりマクロを有効にする必要があります。
Ariawaseを使用したExcelマクロからVBAの分離
今回のレシピ
Windows
Microsoft Excel
コマンドプロンプト
標準の機能として、ExcelマクロはVBAをExportしたりImportすることができます。 この処理を、プログラムで行うvbac.wsfというスクリプトをigetaさんという方がAriawase というツールの中で開発していて今回利用させてもらいました。ありがとうございます。
私の知る限りxlsmでの開発は基本的にずいぶん前から変わっていません。Excel起動後、 開発タブ→Visual Basicの順でエディタを開きます（もしくはAlt-F11）。下記が表示されます。
※Excelのデフォルトでは開発タブが出ていないかもしれません。 その時は、ファイル→オプション→リボンのユーザ設定で開発にチェックを入れるとタブが現れます。
初回のxlsm、ソース分離
作成したエクセルマクロ（*.xlsm）を上記ツール配下のbinディレクトリに配置します。
その後、コマンドプロンプトで、
$ cscript vbac.wsf decombine
これでbinに配置したxlsmの「ファイル名」をディレクトリ名として、VBAソースがExportされます。
↓こんな↓かんじのフォルダ構成
. │ vbac.wsf │ ├─bin │ DBTBL.xlsm ←対象のエクセルマクロ ├─src │ └─DBTBL.xlsm ←この配下が生成されるフォルダ │ AnalizeExcel.cls ←Exportされたクラス │ Automation.bas ←Exportされた標準モジュール │ ExcelDB.cls ←Exportされたクラス │ FileUtility.cls ←Exportされたクラス │ SQL.bas ←Exportされた標準モジュール
さらに、
$ cscript vbac.wsf clear
でbin\DBTBL.xlsmからVBA部分のコードを削除します。
このファイルをtemplateに移動してソース管理を行いました。
git管理時のフォルダ構成
.
│ .gitignore
│ build.bat
│ readme.md
│ vbac.wsf
│
├─.vscode
│ settings.json
│
├─bin
│ .gitkeep
│ DBTBL.xlsm ←成果物
│
├─src ←ソース
│ └─DBTBL.xlsm
│ AnalizeExcel.cls
│ Automation.bas
│ ExcelDB.cls
│ FileUtility.cls
│ SQL.bas
│
└─template ←テンプレートのエクセルマクロ（ボタンや設定シートのみがある）
DBTBL.xlsm
template からbinにコピー後に先ほどのimportを呼び出すbatスクリプト （実質的なビルドスクリプト） [build.bat]
copy template\DBTBL.xlsm bin\ cscript vbac.wsf combine
binのエクセルマクロをVBA IDEで修正後、decombineでsrcソースに反映、 差分の確認や、gitでのソース管理ができます。
作成したマクロ
このツールDbTblExcelはpostgreSQLを対象にExcelにデータを持ってきたり、データを保存できる、マクロです。
つまり自分自身（DBTbl.xlsm）の変更を行います。今のところデータのImportはPKを指定しての、デリートインサートで行っています。
ソース管理できるようになったのでgithubで公開しました。
使い方はこちらに記載しました。
自由にお使い下さい。ですが、DBを直接修正できる、便利だけど、危険なものなので、 もし使用される際はよく動作を確認して使用して下さい。当マクロを使用して発生した損害は一切の責任を負いかねます。
デモ
※日本語字幕にコメントを記載しました。
こちら はマクロそのもののDLリンク
ExcelによるDB接続
ExcelからDBにアクセスする方法には、下記方法があります（たぶん方法的には10年以上変わっていないと思います）。
- ODBC
https://www.postgresql.org/ftp/odbc/versions/msi/
- OLEDB
http://pgfoundry.org/projects/oledb
こちらから対応したbitバージョン *3 のODBCドライバ、OLEDBドライバを取得してください。
※私の環境ではフリーのOLEDBドライバでは、うまくいかないことがありました。ODBCドライバをお勧めします。
おまけ - VisualStudio Codeでの設定 -
VSCodeデフォルト設定の場合、.clsがVisualBasicと判定されていないようなので、下記設定を行えばよいようです。（フォームがあるマクロの場合は.frmも？未確認）
また、Exportされるソースファイルは（日本語版Excelマクロの場合）SHIFT_JISですのでこちらもあわせて設定しておくのが良いです。
.vscode/settings.json
// 既定の設定とユーザー設定を上書きするには、このファイル内に設定を挿入します
{
"files.encoding": "shiftjis",
"files.associations": {
"*.cls":"vb"
}
}
下記のようになります。
このソース管理には問題がある！
かつてxlsmファイルそのものをソース管理していたことがありました、が、バックアアップされているという意味以外では全く無意味でした。
ソース管理できていない状態でのエクセルマクロ開発は本当に苦痛でした。なので作った後どうしてもメンテナンスが続かなかったです。
みなさんもフォルダの片隅にそういうマクロがあるんじゃないですかね？
2016年になっても、VBAって言葉は結構聞きますね。 需要あるんでしょう。
手前みそですが、こんなサイトも。。
今回はこの辺で。
最後に
アスタミューゼでは現在、エンジニア・デザイナーを募集中です。 興味のある方はぜひ 採用サイト からご応募ください。
