Excel ドキュメントの納品時に毎回やっていることを一括自動処理する Excel VBA マクロを作った

Excel で作った設計書などを納品する際にやっていることを一括で自動処理する Excel VBA マクロを作った。

目次

• まずはコード紹介
• このマクロがやれること
• Windows・MacOS ともに動作するクロスプラットフォームを実現
• 他に課題とか
• 以上

まずはコード紹介

先にマクロを紹介する。以下の Gist に上げたので、「標準モジュール」として取り込んでおき、適当な Excel マクロブックから exec() サブプロシージャを呼び出してあげれば OK。

• Format Excel Workbooks · GitHub

コードコメントが英語なのは、Mac の VBEditor で日本語入力できないため。英文法メチャクチャで恥ずかしいから、なるべく個々の関数を簡素に作って、簡単な単語で伝わるようにした…。

以降、説明。

このマクロがやれること

このマクロがやってくれることは以下のとおり。

• 指定のディレクトリ配下にある Excel ファイル群について、次の操作を行う
  • 全てのシートで A1 セルにカーソルを合わせ、シート最上部にスクロールする
  • 全てのシートで拡大倍率を 100% にする
  • 未使用の「名前定義」を削除し、ファイルサイズを削減する
  • 未使用の「スタイル定義」を削除し、ファイルサイズを削減する
• 編集したファイルは指定のディレクトリ配下に Modified ディレクトリを作り、そこに保存するので、元ファイルは汚さない
  • .xls ファイルはマクロの有無を確かめた上で、.xlsx もしくは .xlsm 形式で保存し直し、ファイルサイズを削減する

実は個々の処理は、以前色々な記事で紹介している。

• Excel でシートを開いた時に全シート A1 セルにカーソルを合わせ、必ず1シート目を開かせるマクロ

今回はこれらの処理を一つの「標準モジュール」として統合し、ディレクトリを指定するだけで、配下のエクセルファイルを一気に修正してしまうコードにした。

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 を実行すれば良いだけなので、よしなに。
  • 参考：Excel VBA 改ページプレビュー、ノーマルビュー(標準)、ページレイアウト表示を切り替える -Window.Viewプロパティ-
• サブディレクトリまで再帰的に掘り下げる作りにはしていない。Mac 側は find コマンドの -maxdepth オプションを渡さないように変えるとか、Windows 側は Dir() 関数の再帰呼び出しが必要とか、面倒なのでやめた。
• 実行結果を新規 Excel ブックに書き出すとか？
• 非表示のシートは整形処理を無視している件
  • いじった方がいいのかな。
• あらゆる Excel ファイルの状態での検証ができていない
  • シートの保護があったらどうなるかな、とか、そういうところ。
• .xlb・.xlsb ファイルは無視している
  • 今どきバイナリブックにするヤツいるのか？と思って無視した。
• ファイルの作成日時・更新日時もいじりたいって？
  • Mac なら setfile と touch コマンド、Windows なら PowerShell で Set-ItemProperty を使って CreationTime と LastWriteTime を変更してください
  • (リリース後に神 Excel を書き直して日付を過去日にズラして納品とかブラックかよ…笑)

以上

操作対象のファイル一覧を特定するための処理部分が、クロスプラットフォーム対応のためになかなか苦戦した。

明らかなバグや追加要望等が挙がれば、上述の Gist を GitHub リポジトリに移して、もう少し開発してみようかなと思う所存。アドバイスなんかもあったらぜひください。