Mac で Excel VBA を使って日本語を含むフォルダを一括作成する

はじめに

Mac (macOS) でも Excel で VBA によるスクリプトは動くのですが、スクリプトの中に日本語を入れたり、スクリプトで日本語の文字が入ったデータを扱うと、期待しない結果（文字が欠落したり、アンダーバー (_) や クエスチョンマーク (?) に置き換わったりする）になります。このように、Windows PC であれば問題なく動くスクリプトでも、Mac ではそのまま動かないことがあります。

本記事では、Windows 上の Excel で動く VBA スクリプトと同じ動作を Python のスクリプトに置き換えることで、Mac で Excel VBA で日本語を扱う処理を安全に行う方法を紹介します。

3行まとめ

すでに Mac に Excel はインストールされているとします。

1. Mac に Python の実行環境をインストールし、Python スクリプトを置く
2. Excel VBA から Python スクリプトを呼べるように AppleScript を決まった位置に置く
3. Excel 上にマクロ呼び出しのボタンを作成して、実行する

確認した環境

本記事でのスクリプト等は、以下の環境で動作を確認しています。

• macOS 11.6 Big Sur と 12.5 Monterey
• Excel 2016 (Microsoft 365 Subscription)
• Python 3.9 または 3.10

想定する活用シナリオ

Excel で作成したいフォルダ名を1行に1つずついれておき、マクロ呼び出しボタンを押したら、Mac 上にフォルダを一括で自動作成するように作りたい。

例えば、人事部に所属する人の定型作業として、その月に入社した社員へ必要な資料やデータなどを配布するときに、クラウドストレージ上にそれぞれの社員に対応するフォルダを作成したい場合などです。

ただし本記事では、クラウドストレージにアップロードする部分や、それぞれのフォルダの権限設定（本人しかアクセスできないようにする等）は含みません。
前提として、クラウドストレージのデスクトップアプリ等で、自動で同期 (sync) する仕組みが別途動いていることとします。

Excelのデータの例

作成するフォルダはExcelで以下のように指定します。１行目はヘッダーとして用途をいれてあります。A列には、作成したい親フォルダ位置を Mac の方式で指定しています。qiitauser の部分は各自のログイン名に置き換えてください。Work/社員用/2022年X月 の部分も、各自で作成した場所に書き換えることができます。

A列、B列の空白のセルは、直前の行と同じ値になることを意味します。このデータの場合は、6月のフォルダに4人(営業部が2人と開発部が2人)、7月のフォルダに1人を作るように指定しています。

行	A 列	B 列	C 列	D 列	E 列
1	親フォルダ	接頭辞	社員番号	氏名	接尾辞
2	/Users/qiitauser/Work/社員用/2022年6月	【営業部】	1111111	佐藤一郎	A
3			1112222	山田二郎	B
4		【開発部】	2223333	鈴木三郎	C
5			2224444	山本四郎	D
6	/Users/qiitauser/Work/社員用/2022年7月	【営業部】	1113333	髙橋五郎	A

Mac に Python の実行環境をインストールし、Python スクリプトを置く

Python 実行環境のインストール

英語サイト https://www.python.org/downloads/macos/ の "Stable Releases" の先頭にあるバージョン（執筆時点では Python 3.10.5) をダウンロードします。

または、日本語サイト https://pythonlinks.python.jp/ja/index.html の "macOS" タブで先頭にあるバージョン（同上）をダウンロードします。

pkg ファイルのインストール手順の詳細は割愛します。

Python ライブラリのインストール

続いて、Terminal を開いて、必要な Python ライブラリをダウンロードします。今回は、Python から Excelファイルを扱うライブラリ openpyxl を用います。

pip install openpyxl

openpyxl の詳細については、こちらにて確認できます。

Python スクリプトを作成する

以下のコード例で示す create-folders.py というファイルを作り、適当なところに置きます。今回は /Users/qiitauser/Work に置くことにします。テキストエディタ等に貼り付けて保存してください。

create-folders.py

#
# Excelファイルの内容に応じて、フォルダを一括作成するスクリプト
#

import argparse
import openpyxl as xl
import os
import pathlib

def main():
    # 引数を1つ受け取るように指定します。引数は args.inputfile という名前で参照します
    parser = argparse.ArgumentParser()
    parser.add_argument("inputfile", type=str, help="Input Excel file relative to 'scripts'")
    args = parser.parse_args()

    # Excel ファイルを開きます
    wb = xl.load_workbook(args.inputfile)

    # シートを指定します。1個しかないので、とくに名前では指定しない
    sheet = wb.active

    # 行番号は 1 からスタート。
    row_idx = 1

    parent = None
    prefix = None
    postfix = None
    print(f"Attempt: {sheet.max_row-1} folders")

    # 成功した数のカウント
    succ = 0

    # 失敗した数のカウント
    fail = 0

    # シート内にあるすべての行に対して繰り返す
    for row in sheet:

        # 1行のセルを、左から順に代入する
        [c_parent, c_prefix, c_serial, c_name, c_postfix] = row

        # 1行目は列名なのでスキップ (row_idx が2以上のときのみ実行)
        if row_idx > 1:

            # 「親フォルダ」に値があったらセットする
            if c_parent and c_parent.value:
                parent = c_parent.value

            # 「接頭辞」に値があったらセットする 
            if c_prefix and c_prefix.value:
                prefix = c_prefix.value

            # 社員番号
            serial = c_serial.value

            # 氏名
            name = c_name.value

            # 「接尾辞」に値があったらセットする
            if c_postfix and c_postfix.value:
                postfix = c_postfix.value

            # 作成するフォルダを文字列で連結します
            path_value = f'{parent if parent else ""}/{prefix if prefix else ""}{serial}{name}{postfix if postfix else ""}'

            # フォルダを作成するためのオブジェクト
            target_dir = pathlib.Path(path_value)
            try:
                # フォルダを作成。上のほうの親フォルダがなかったら同時に作成。
                # またすでに存在していてもエラーにしない
                target_dir.mkdir(parents=True,exist_ok=True)

                # 成功数を1つアップする
                succ += 1
            except:
                # print(f"フォルダ作成失敗：{path_value}")
                print(f"Fail: {path_value}")

                # 失敗数を1つアップする
                fail += 1
        # 列番号を1つアップ（1行目かそれ以外かの判定にしか使ってません）
        row_idx += 1

    # 全体の終了メッセージ
    print(f"Completed. Success: {succ} folders, Failed: {fail} folders")

if __name__ == "__main__":
    # この Python スクリプトが置いてあるところに、実行時の環境を移動する
    # Excelの「親フォルダ」で出力したい場所の全パスを指定してください
    os.chdir(os.path.dirname(os.path.abspath(__file__)))
    main()

Python を呼び出すシェルスクリプトを作成する

同様に、この Python スクリプトをExcelファイル名を指定して呼び出すシェルスクリプト run-python.sh も作成します。こちらも /Users/qiitauser/Work に保存します。

run-python.sh

/Library/Frameworks/Python.framework/Versions/3.10/bin/python3 ${1}/create-folders.py ${1}/employee-sample.xlsm

Excel VBA から Python スクリプトを呼ぶように Apple スクリプトを決まった位置に置く

まずは、以下の内容のファイルをテキストエディタ等に貼り付けて、作業フォルダである /Users/qiitauser/Work に CreateFolders.applescript というファイル名で保存します。

CreateFolders.applescript

on createFolders(workingDir)
   set stdout to do shell script "sh " & workingDir & "run-python.sh " & workingDir
   return stdout
end createFolders

次に、これを決まった位置に移動させます。Mac の Finder では見えない位置に移動させるので、Terminal を開いて、以下のコマンドを入れます。

cp ~/Work/CreateFolders.applescript ~/Library/Application\ Scripts/com.microsoft.Excel/

Excel 上に、マクロ呼び出しのボタンを作成する

Excel上で、フォルダのデータを作成したあと、マクロを呼び出すボタンを作成します。

1. 挿入 ＞ 図形 から「角丸四角形」などの図形を、E列の右側の空いているところに作成します
2. 作成した図形に「フォルダ作成」などのテキストをラベルとして挿入します
3. 作成した図形を右クリックして「マクロの登録...」をクリックします
4. 「マクロの登録」ウィンドウで、「新規作成」を押します
5. 下に示す CreateFolders.vba の内容をコードのところに貼り付けます。(Sub ... から End Sub まで含めて全体を置き換えます)
6. exmployee-sample.xlsm という名前で作業フォルダ /Users/qiitauser/Work に保存します。

CreateFolders.vba

Sub Run_Python_Script()
Dim output As String
output = AppleScriptTask("CreateFolders.applescript", "createFolders", "/Users/qiitauser/Work/")
MsgBox output
End Sub

ボタンを作成したあとのExcelファイルは下の図のようになります。（諸事情によりA列の幅を狭めていますがご了承ください）

作成したファイルまとめ

ファイルの位置は、/Users/qiitauser を ~ で省略しています。

種類	ファイルの位置
作業フォルダ	~/Work
Python スクリプト	~/Work/create-folders.py
シェルスクリプト	~/Work/run-python.sh
Apple スクリプト	~/Library/Application Scripts/com.microsoft.Excel/CreateFolders.applescript
Excel ファイル	~/Work/employee-sample.xlsm
Python 実行環境	/Library/Frameworks/Python.framework/Versions/3.10/bin/python3

実行してみる

Excelファイルを改めて開きます。シート内にある「フォルダ作成」ボタンを押すと、スクリプトが動き出します。完了したら以下のような結果を示すダイアログが表示されます。（※注 日本語を表示しようとすると文字化けしてしまうので、英語です）

Excelで指定したフォルダの内容は、このように作成されます。

実行時エラー '5' が出たとき

以下のようなエラーが出た場合は、今回使用しているファイルの位置や内容が間違っている可能性があります。

「作成したファイルまとめ」を参照して、Excelファイル名、作業フォルダ、Python 実行環境などを確認してみてください。

カスタマイズのヒント

Excelのファイル名を変えたい

exmployee-sample.xlsm から別のファイル名で保存した場合は、run-python.sh のファイル名の部分も変更してください。

実行する場所を変えたい

/Users/qiitauser/Work とは違う場所にスクリプト一式やExcelファイルを置いて実行する場合は、Excelファイルの」マクロを登録...」メニューから、編集を選択して、以下の「新しい作業フォルダ」のところにパスを指定します。

output = AppleScriptTask("CreateFolders.applescpt", "createFolders", "新しい作業フォルダ")

Python を別の方法でインストールしている

Terminal を開いて、コマンド which python3 を実行して、どの python3 が使えるか確認してください。その後、run-script.sh の python3 のパスを確認したパスに置き換えてください

詳細はこちらのページを参照ください。

A列の親フォルダを直接クラウドストレージに指定したい

例として Box であれば、/Users/qiitauser/Library/CloudStorage/Box-Box/ がデフォルトの同期先フォルダになりますので、これを含む形で親フォルダを指定すれば、自動的にアップロードされるはずです。もちろん、Python スクリプトやExcelファイル自体はこの場所に移動させなくても動作します。

フォルダ名の構成を変更したい

B, C, D, E列のつなぎ方を変更したり、アンダーバー(_)などで区切りたい場合は、以下の行を編集します。この例は、アンダーバーで連結したい場合です。

            # 作成するフォルダを文字列で連結します
            path_value = f'{parent if parent else ""}/{prefix if prefix else ""}_{serial}_{name}_{postfix if postfix else ""}'

作成したそれぞれのフォルダにファイルをコピーしたい

create-folders.py の　def main(): の行より上に、コピーしたいファイルなどを指定します。(作業フォルダにすでに同じファイルがあることを想定しています）

import shutil

copy_files = ['自己紹介.docx','研修内容.pdf']

さらに、「成功数を1つアップする」の前に、以下のように3行の for 文を入れます。

                target_dir.mkdir(parents=True,exist_ok=True)

                for file in copy_files:
                    src_file = pathlib.Path('./' + file)
                    shutil.copy(src_file, target_dir)

                # 成功数を1つアップする

その他

• なぜ Python が必要なのですか？

Python でなければならない強い理由はありません。ほかのプログラミング言語でも同じことができます。

• 仕組みについて詳しく知りたい

こちらのサイトに仕組みの解説があります。