目的
- バッチを外部から起動するアプリがある。
- バッチの指定がうまくいっているかどうかを手軽に確認したい。
- どこから呼び出したかわかるように、引数も設定したい。
- そこで、バッチが動いたかどうかが判断できる簡単なプログラムを作る。
カテゴリー: Microsoft
Windows や Office など、Microsoft 社関連の話題ですわ♪
手元の開発環境、MacBook Pro Retina 2012 での Boot Camp で Windows 10 Pro 64bit、で試験やイントラネット内でウェブサービスをテストしたり使ったりすることを念頭に、前回は IIS を有効にして外部 PC やスマホのウェブブラウザからアクセスできるようにいたしました。
ウェブアプリ、ウェブサービスといえば、データを永続的に保存するのにデータベースを使用する機会が多くございます。
ですので、今回は開発環境のローカル PC である Windows 10 Pro 64bit に SQL Server 2014 Express と、その管理ツールである SQL Server Management Studio Express を導入いたします♪
SQL Server Management Studio (SSMS) を利用したインポート手順
- C:¥Program Files¥Microsoft SQL Server¥MSSQL12.SQLEXPRESS¥MSSQL¥Backup にバックアップ DB ファイルを配置
- SSMS 起動
- [データベース] を右クリック > [データベースの復元]
- [ソース] は [デバイス] を選択。[…] > [追加] > バックアップ DB ファイルを選択し、[OK]
- データベースの復元ウインドウで [OK]
ポイント
- エクスポートという言葉を使っているが、SQL Server Management Studio の機能の名前としてはバックアップ
- バックアップ先のデフォルト: C:¥Program Files¥Microsoft SQL Server¥MSSQL12.SQLEXPRESS¥MSSQL¥Backup
- バックアップファイルのデフォルト拡張は bak
SQL Server Management Studio (SSMS) を利用したエクスポート手順
- SSMS 起動
- [データベース] > エクスポート対象 DB を右クリック > [タスク] > [バックアップ]
- 表示される [データベースのバックアップ] ウインドウで次を選択
- データベース: バックアップしたい DB を選択
- 復旧モデル: 単純 ← 変更不可だった
- バックアップの種類: 完全
- コピーのみのバックアップ: チェックしない
- バックアップコンポーネント: データベース
- バックアップ先: ディスク
- パス: C:¥Program Files¥Microsoft SQL Server¥MSSQL12.SQLEXPRESS¥MSSQL¥Backup¥DBName.bak ← 自動で表示
- [OK]
作ったウェブアプリを試験してみたい！それもローカルネットワーク内で！という時には手元にある Windows PC をウェブサーバーとする他ありません。
ですので ASP.NET を使えるようにオプションをつけた IIS をセットアップし、外部からアクセスするまでをノートいたします♪
なお、ライセンスに関しては一切考慮しておりませんので、一般に公開する際は十分に注意が必要かと存じます。
環境
- MacBook Pro Retina 2012 での Boot Camp
- Windows10 Pro 64bit
IIS と ASP.NET の有効化と動作確認
やりたいこと
- 12月の翌月は1月だが、この数字を簡単に VBA で取得したい。
- 月によって最終日が異なるが、月末日の数字を簡単に VBA で取得したい。
ポイント
- DateSerial メソッドを使用する。引数は、年、月、日、の数字。
- DateSerial メソッドは 1〜12以外の月数字や、1〜28、29、30、31以外の日にち数字とした場合、はみ出た分を自動的に加減して計算してくれる。
- 例えば、DateSerial(2015, 14, 0) ⇒ 2016/01/31 (14月 → 翌月2月 & 0日 → 前日末日) となる。
- 来月の月数字 (y は年の4桁文字列、m は月の数字文字列) :
month(DateSerial(y, m + 1, 1)) - 今月末の日にち数字 (y は年の4桁文字列、m は月の数字文字列) :
day(DateSerial(y, m + 1, 0))
VBA コード
追記: 更に改善版です！
フォーマットに指定した変数に対応する場所に書き込むことでセルの位置、つまり座標を意識しないでよくなるクラスを書きました。
抽象度が高まり、よいと存じます。
今回は、これを改善いたしましたので、それを記録いたします♪
ポイント
- 利用者が誤ってテンプレートの変数を削除した場合でもエラーとさせたくない。そのために、CellValues に設定していたキーに対応するテンプレートになくても無視するようにした。
- 書き込み対象ワークシートに書き込んだ後、書き込み先範囲を移動するメソッドを追加した。これにより、ループを使って同じフォーマットの複数の帳票を作成できる。
- WriteToVariableAndOffset → オフセット移動
- WriteToVariableAndDown → 下に移動
- プロパティにしなくとも問題ないものはプロパティから削除し、スリム化した。
- 省略しても可読性が維持できると思った部分は短く書き、行数を短くした。
次の点は、あえて対応いたしませんでした。
- フォーマットの変数に対応する値がコードに設定されていない場合は、テンプレートの変数がそのまま書き込み先シートに書き込まれてしまう。
- このとき、特にエラーは発生しない。
- コード側でのフォーマット変数定義漏れに気がつくチャンスが増えるメリットがある。
- また、コード側で値が取得できないことがあると予めわかっている場合は、空文字で初期化しておけば良い。
- よって、特に対処しない。
追記: 改善版です！
- 【Excel VBA】【改善】フォーマットワークシートに指定した変数に対応する場所に書き込むクラスのリファクタリング・機能追加いたしました！ – oki2a24
- 【Excel VBA】【改善2】座標でなく、フォーマットシートに設定したキーに対応する場所に書き込むクラスの仕様変更とリファクタリング♪ – oki2a24
なぜ実現したかったのか？
- エクセル方眼紙での帳票は、ちょっとデザイン変更すると簡単に絶対座標がずれてしまい、VBA の座標修正が辛い。
- ExcelCreator のように、ワークシートに変数として入力したセルの内容を書き換えて出力したら楽になると考えた。
- 製品情報 – ExcelCreator 2012 | アドバンスソフトウェア株式会社
- “A1 参照形式”、”座標形式”、”セルの名前”、”変数名”の 4 つの方法で値を差し込むセルの指定が可能。”変数名”での差し込みを目指したのが本投稿
- セルの座標を気にする必要がなくなることがメリット
- テンプレートのワークシートのデータを反映したいセルに変数を書き込めば良い。座標は気にしなくて良い。
- VBA ではセルの変数に対してデータを差し込めば良い。座標を指定しなくて良い。
- セルの名前を使う方法もある。ただし、セルの名前は扱いやすい形でワークシートに表示することができず、辛い。
処理の流れ
ワークシートテンプレートのセルに記入する変数と、その座標と、VBA に書くワークシートテンプレートの変数名と、代入する値をどうやって紐付けるかが重要です。
次に挙げるポイントの考えでつなげるようにいたしました。
ポイント
- テンプレートの変数をキーに、そこに対応するワークシートの座標を値にして VBA で取り込んでディクショナリ 1 を作る。
- テンプレートの変数をキーに、そこに当てはめる値を値にしてのディクショナリ 2 を作る。
- ディクショナリ 2 をループして、テンプレートの変数を仲立ちにして、変数に当てはめる値と、ワークシートの場所をひも付け。
- ワークシートへの書き込みは、2次元配列を Range に代入して 1 回で行う。速さを保つ。
もう少し具体的にしますと、次のようになります。
CSV ファイルのをエクセルにインポートする条件
- CSV ファイルを VBA からエクセルにインポートしたい。
- CSV の各項目にダブルクオーテーションが付いていてもいなくても、インポートできるようにしたい。
- 読み込み先エクセルの列のデータ型、つまり表示形式を指定したい (先頭の 0 が自動的に除去されないように、などしたい) 。
読み込まれる CSV ファイル
No,氏名,電話番号 1,伊藤健一,0112223333 2,高橋哲也,0114445555 3,佐藤誠,0116667777
VBA コード
- クエリーテーブルを使用する方法で CSV をインポートしている。
- クエリーテーブルを使用してインポートする場合、次の副作用、想定外の出来事、悪影響が発生する。
- インポート対象 CSV ファイルとのコネクションが張られてしまう (エクセルで、データ > 接続、で確認可能のはず) 。これにより、エクセルに読み込んだデータを編集すると元データも同期されてしまう。
- インポート範囲を対象に Name オブジェクトが作成されてしまう (エクセルので、数式 > 名前の管理、で確認可能) 。これといって害は無いと思う。
- 副作用を取り除くために、インポート処理完了後に次の処理を行った。
- コネクションはすべて削除するようにした。
- Name オブジェクトは自動的に作成されたものを狙って削除するようにした。他にもセル範囲に名前をつけた Name オブジェクトが予め存在していたため。
- 自動生成の Name オブジェクトは、名前を指定しても連続して CSV インポートを行うと接尾辞が付与された Name オブジェクトが追加されてしまう。これにより、指定した文字列で Name オブジェクトを削除できない場合がある。
- そこで Name オブジェクトが生成されるときに接尾辞を予め付与することで自動付与の接尾辞を抑制した。接尾辞のフォーマットは、他の Name オブジェクトと重複しないよう yyyymmddhhnnss とした。
- データの先頭に 0 がある場合、取り除かれてインポートされてしまう。そこで、TextFileColumnDataTypes で各列に適用されるデータ型を指定できるようにしている。
- クエリーテーブルの各プロパティについて、必須かどうか、デフォルト値は何か、は調べていない。省略できるプロパティはあると思う。
- TextFilePlatform の値は 932 で、これはマクロの記録で設定された値をそのまま使用している。公式ドキュメントのページを見ても、932 に該当する値は記載されていない。しかし次のページを見てみると正しい値と思うのでこのまま使用する。
VBA コード
- CollectionToArrary の返却値は Variant だが、本当はどんな型なのか限定させたかった。。。
- 汎用性を考慮して、また、Arrary(“い”, “ろ”, “は”) などで配列を生成するときは Variant の変数とすることを考慮して、CollectionToArrary の返却値は Variant に決定した。
- ループ内で ReDim して配列サイズを毎回変更することはしないようにした。
- 要素を回すのに、元となるコレクションをループする方法と、作られる配列をループする方法とがある。今回、コレクションをループする方法を採った。
- コレクションのインデックスは必ず 1 から始まる。配列のインデックスの開始は配列の定義時に自由に設定できる。
- 配列をループしてそのインデックスに対応するコレクションのインデックスを計算して取り出すよりも、コレクションをループして配列の開始インデックスをインクリメントしていく形で要素を詰め込んでいく方がシンプルに書ける。
- よって、ループする前に LBound で配列の最低のインデックスを取得して変数に代入しておき、ループ内の最後で変数を + 1 することで配列の添字を動かしていく。