[小ネタ]PythonでVBAを含むExcelファイルを扱う(OpenPyXL)

2020.10.26

はじめに

データアナリティクス事業本部のkobayashiです。

以前OpenPyXLでExcelファイルを読み込む記事を書きましたが、VBAを含むxlsm形式のExcelファイルをテンプレートファイルとして読み込み、特定のセルにOpenPyXLで値を書き込んだ後に別ファイルに書き込むという事を行いましたのでその内容をまとめます。

以前の記事

OpenPyXLのリポジトリ : openpyxl / openpyxl · GitLab

環境

  • Python 3.7.4
  • OpenPyXL 3.0.0

インストール

以前の記事でインストール方法を記載しましたが一応インストール方法をまとめておきます。

pipを使いインストールします。

pip install openpyxl

# Excel内の画像ファイルを扱う場合はpillowライブラリが必要なのでインストールしておきます。
pip install pillow

Excelファイルの読み込みと書き込み

load_workbookメソッドの解説

ExcelファイルをOpenPyXLで読み込むときにはload_workbookメソッドを使いますがxlsmファイルを扱う場合はここでkeep_vbaパラメータを指定する必要があります。他にもload_workbookメソッドにはオプションがあリます。

openpyxl.reader.excel.load_workbook(filename, read_only=False, keep_vba=False, data_only=False, keep_links=True)
パラメータ 説明
filename 型: String
必須
読み込むファイル名を指定する
read_only 型: Boolean
デフォルト: False
エクセルファイルを読み取り専用で開く
編集する必要がない場合に指定することでロード時間が大幅に短縮できる(読み込み時間が半分以下になります)
keep_vba 型: Boolean
デフォルト: False
VBA保持したまま保存する場合にTrueにして読む込む
data_only 型: Boolean
デフォルト: False
数式を含むセルの挙動を制御する
数式(デフォルト)のままで扱うか、Excelが最後にシートを読み取ったときに保存された値を使うかを指定する
keep_links 型: Boolean
デフォルト: True
外部ワークブックへのリンクを保持するか否かを指定する

今回はVBAを含むxlsmファイルを扱うのでkeep_vba = Trueを指定してload_workbookを使います。

公式ドキュメント:openpyxl.reader.excel module — openpyxl documentation

VBAを含むExcelファイルテンプレートの確認

テンプレートに使うExcelファイルは下図のようにマクロを有効にして開くとsheet1A1セルとB1セルの合計をC1セルに入力します。

テンプレートファイルの初期値

テンプレートファイルの初期値

VBAの中身

VBAの中身

マクロを有効にして開いた状態

マクロを有効にして開いた状態

ではこのファイルをOpenPyXLを使ってこのファイルに値をPythonで書き込みます。

OpenPyXLでファイルを読み書きする

使うスクリプトの中身は以下のコードを使います。

import openpyxl

def main():
    # set
    wb = openpyxl.load_workbook("./source.xlsm", keep_vba=True)
    sheet = wb["Sheet1"]
    sheet.cell(row=1, column=1, value=10)
    sheet.cell(row=1, column=2, value=20)
    sheet.cell(row=1, column=3, value=99)
    wb.save("./target.xlsm")

if __name__ == '__main__':
    main()

ではこのスクリプトを実行して作成したファイルを確認してみます。 マクロを有効にして開いた場合はVBAが実行されてC1セルの値がA1セルとB1セルの合計値に書き換わり、VBAで再計算が行われていることが変わります。

一方マクロを無効にして開いた場合はPythonスクリプトで指定した値の99C1にそのまま表示されていて、VBAでの再計算が行われていないことがわかります。

因みにのkeep_vbaを指定しない(wb = openpyxl.load_workbook("./source.xlsm"))で上記のコードを実行すると一応エラーがなくPythonの実行は終わりますが、開こうとすると下図のエラーが出てファイルが壊れてしまい開けません。

まとめ

VBAを含んだxlsm形式のExcelファイルでもOpenPyXLでkeep_vbaパラメータを使うことでファイルを破損すること無く値を書き込み保存できることがわかりました。

最後まで読んで頂いてありがとうございました。