Excel ドキュメントの納品時に毎回やっていることを一括自動処理する Excel VBA マクロを作った
Excel で作った設計書などを納品する際にやっていることを一括で自動処理する Excel VBA マクロを作った。
目次
まずはコード紹介
先にマクロを紹介する。以下の Gist に上げたので、「標準モジュール」として取り込んでおき、適当な Excel マクロブックから exec()
サブプロシージャを呼び出してあげれば OK。
コードコメントが英語なのは、Mac の VBEditor で日本語入力できないため。英文法メチャクチャで恥ずかしいから、なるべく個々の関数を簡素に作って、簡単な単語で伝わるようにした…。
以降、説明。
このマクロがやれること
このマクロがやってくれることは以下のとおり。
- 指定のディレクトリ配下にある Excel ファイル群について、次の操作を行う
- 全てのシートで A1 セルにカーソルを合わせ、シート最上部にスクロールする
- 全てのシートで拡大倍率を 100% にする
- 未使用の「名前定義」を削除し、ファイルサイズを削減する
- 未使用の「スタイル定義」を削除し、ファイルサイズを削減する
- 編集したファイルは指定のディレクトリ配下に
Modified
ディレクトリを作り、そこに保存するので、元ファイルは汚さない.xls
ファイルはマクロの有無を確かめた上で、.xlsx
もしくは.xlsm
形式で保存し直し、ファイルサイズを削減する
実は個々の処理は、以前色々な記事で紹介している。
今回はこれらの処理を一つの「標準モジュール」として統合し、ディレクトリを指定するだけで、配下のエクセルファイルを一気に修正してしまうコードにした。
Windows・MacOS ともに動作するクロスプラットフォームを実現
キモとなる整形処理は上述のとおりほとんどコードを用意してあったのだが、「複数ファイルを取得する」ために使用していた Dir()
関数が Excel for Mac では動作しないため、MacOS でも動作するよう、対象ファイルの取得方法を OS 別に用意した。
OS の判定は If Application.OperatingSystem Like "*Mac*" Then
で行える。Mac の場合は AppleScript を利用してディレクトリ選択ダイアログを表示し、続いて AppleScript 経由でシェルスクリプトの find
コマンドを実行し、Excel ファイルのフルパスを取得した。
Windows の場合は通常どおり Dir()
で取得。Mac 側で、Excel ファイルのフルパスを配列で返していたので、戻り値の型を合わせるため、Dir()
関数の結果をフルパスに変換し、配列で返すようにした。本当は Application.FileDialog(msoFileDialogFolderPicker)
とかを使って、Windows でもディレクトリ選択ダイアログを表示させたかったのだが、Mac で実行すると msoFileDialogFolderPicker
の参照を解決するための参照設定が追加できず断念。Windows の場合は Application.InputBox
を表示させて、対象ディレクトリへのフルパスを入力してもらうようにした。ちょっと使い勝手悪いかな。
他にも、パスの区切り文字が違ったりとなかなか難儀だったのだが、Excel ファイルを開くところまで行ければ後は問題なし。コレで Windows でも Mac でも使える Excel VBA マクロになった。
他に課題とか
とりあえずやりたいことはやれたのだが、他に課題というか、直せそうなところでいうと、以下のとおり。
- 拡張子を
.XLSX
など、大文字で書いている場合は上手く扱えない件。LCase()
とかで小文字にして判定したりすれば良いかな。 - 「改ページプレビュー」表示などではなく、「標準ビュー」に戻したい、とかいう需要があるかも?シートごとに
ActiveWindow.View = xlNormalView
を実行すれば良いだけなので、よしなに。 - サブディレクトリまで再帰的に掘り下げる作りにはしていない。Mac 側は
find
コマンドの-maxdepth
オプションを渡さないように変えるとか、Windows 側はDir()
関数の再帰呼び出しが必要とか、面倒なのでやめた。 - 実行結果を新規 Excel ブックに書き出すとか?
- 非表示のシートは整形処理を無視している件
- いじった方がいいのかな。
- あらゆる Excel ファイルの状態での検証ができていない
- シートの保護があったらどうなるかな、とか、そういうところ。
.xlb
・.xlsb
ファイルは無視している- 今どきバイナリブックにするヤツいるのか?と思って無視した。
- ファイルの作成日時・更新日時もいじりたいって?
- Mac なら
setfile
とtouch
コマンド、Windows なら PowerShell でSet-ItemProperty
を使ってCreationTime
とLastWriteTime
を変更してください - (リリース後に神 Excel を書き直して日付を過去日にズラして納品とかブラックかよ…笑)
- Mac なら
以上
操作対象のファイル一覧を特定するための処理部分が、クロスプラットフォーム対応のためになかなか苦戦した。
明らかなバグや追加要望等が挙がれば、上述の Gist を GitHub リポジトリに移して、もう少し開発してみようかなと思う所存。アドバイスなんかもあったらぜひください。