このブログはアフィリエイト広告を利用しています

python

Microsoft AccessのVBAマクロをPythonにする方法

xlwingsとは、ExcelマクロをPythonで記述できちゃうという画期的なインターフェイス。
そう思っていませんか？

それ、半分正解です。

あとのもう半分は、Accessなど他のMicrosoft Office製品からもPythonを使うことができるようになる、という点です。

ただ、現状は公式サイトに書かれている手順をそのまま実行してもうまく動いてくれません。

このページでは、xlwings本体のVBAコードを手直ししつつ、動作するように書き換える方法をシェアしたいと思います。

尚、このページの内容は、既にxlwingsを使ってExcelとPythonを連携したことがあり、またその環境も整っている方を読者と想定しています。

まだxlwingsを使ったことが無いという方は、別ページのxlwings入門を一読下さい。

注意

実は僕は、VBAにあまり詳しくありません。

というのも、VBAは極力使いたくないし、勉強なんてもってのほかと考えているからです。

このページでしているハックは、実はもう少しスマートなやり方があるのではないか？と思ってはいるものの、最小手で済ますことができるということでVBAに固定値を書いてしまうという手段をとっています。

xlwingsのバージョン0.17.1としての説明になります

今回は、xlwingsが自動生成するVBAスクリプトを少しハックします。

Microsoft Accessではきちんと動作してくれない部分を固定値で書き込む、という単純な作業になりますが、このVBAスクリプト部分がxlwingsのバージョンによって徐々に複雑化しているようで、このページ執筆時点での最新は0.19.4になっていますが、Accessで動かすために必要な作業がさらに多くなります。

ですから、このページで説明する内容はxlwings0.17.1を前提の説明です。

手を動かしながら読み進める場合はまずxlwingsのバージョンを0.17.1に合わせておくことをお勧めします。

AccessとPythonの連携はUDFsで行う

Excelの場合、xw.Book.caller()からExcelのシートを取得して、Excelを操作するといったことができました。

しかし、xlwingsのAPIにはAccessを操作するものは用意されていません。

そのため、AccessとPythonの連携はUDFsで行う、つまり関数を呼び出してなんらかの処理をさせ、その戻り値を取得するような形でのみ可能です。

このようにあらかじめPythonで作っておいたMyDefinedFunc()関数を呼び出すイメージです。

UDF関数を作成する

（自作関数の作成は、xlwings入門でも説明しています。）

まずは自作関数作成用プロジェクトを作成。
quickstartコマンドを使用して、MakeUdfフォルダ・MakeUdf.xlsm・MakeUdf.pyを作成します。
（MakeUdfという名前はお好みで）

>xlwings quickstart MakeUdf

作られたファイル：

MakeUdf
  |- MakeUdf.xlsm
  |- MakeUdf.py

MakeUdf.pyのmain()関数とif __name__ == "__main__"のブロックはUDFには不要なので削除します。
残すのはhello()関数の部分だけで、ここの名前とシグネチャを変更します。

import xlwings as xw


@xw.func
def だぉ(str):
    return str.replace("です。", "だぉ。")

今回は、語尾を"だぉ。"にするというイカした関数を作成してみました。

次に、MakeUdf.xlsmを開き、[xlwingsリボン]→[fx Import Functions]とクリック

ノート

xlwingsリボンが表示されていない方はxlwingsコマンドでアドインをインストールしてからExcelを再起動して下さい。

xlwings addin install

VisualBasicエディターを開くとxlwings_udfsモジュールが作成されているので、それをエクスポートします。

ファイル名は変更してもいいのですが、ここではxlwings_udfs.basのまま保存しましょう。

UDF関数をインポートする

自作関数をインポートして使用するためのAccessプロジェクトを作成しましょう。

僕の環境では、"E:\PyCharmProjects\HelloAccessWings\UseUdf\Udf使うマス.accdb"を作成しました。

そして同じフォルダに、前項で作成したxlwings_udfs.basとMakeUdf.pyをコピーして持ってきます。

ファイル構成はこんな感じ。

UseUdf
  |-Udf使うマス.accdb
  |-MakeUdf.py
  |-xlwings_udfs.bas

Udf使うマス.accdbを開き、VisualBasicエディタを開きます。

そしてプロジェクトの[Database（Udf使うマス）右クリック]→[ファイルのインポート]を選択

同じフォルダのxlsings_udfs.basをインポートすると、標準モジュールに追加されます。

次に、xlwings.basをインポートします。

このファイルはxlwingsがインストールされたフォルダにあるのですが、xlwings.__path__から確認することができます。

PS C:\Users\gao> python
Python 3.8.3 (tags/v3.8.3:6f8c832, May 13 2020, 22:37:02) [MSC v.1924 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import xlwings
>>> xlwings.__path__
['C:\\Users\\gao\\AppData\\Local\\Programs\\Python\\Python38\\lib\\site-packages\\xlwings']

xlwings.basの場所を突き止めたら、もう一度、[Database（Udf使うマス）を右クリック]→[ファイルのインポート]選択
xlwings.basを選択します。

この後、インポートした2つの.basの中身を修正していきます。

xlwings_udfsモジュールをMicrosoft Access用に修正

xlwings_udfsモジュールを開くと、MakeUdf.pyで作成した だぉ()関数 のシグネチャが書かれています。
また、Const App = "Microsoft Excel"となっているなど、Excelが前提の記述になっているため、Microsoft Accessで使うために修正します。

実はよくわからない部分もあるのですが(笑)
とりあえずAccessで自作関数を呼び出すなら次のような感じで常にPy.CallUDF()関数を引数3つで呼び出せば大丈夫のようです。

※念のためConst Appを"Microsoft Excel"から"Microsoft Access"に変更しておきましょう。

xlwings.basモジュールをMicrosoft Access用に修正

xlwings.basモジュールを開くと、xlwingsの中身を見ることができます。

この中では、実行ファイルがExcelであることを前提にした記述が多いため、Accessから実行している場合はエラーになってしまいます。
そのエラーになってしまう部分というのは、ファイルパスを取得する系の処理なので、この部分を自分のプロジェクトに合わせて固定地のパスを返すように書き換えればうまくいきます。

GetDirectoryPath()関数を書き換える

関数内の処理は全て削除して、Accessファイルがあるフォルダを直接指定します。
（説明を簡単にするためにAccessファイルを移動しても動くような工夫は自分で考えて下さいｗ）

Function GetDirectoryPath() As String
    GetDirectoryPath = "E:\PyCharmProjects\HelloAccessWings\UseUdf"
End Function

XLPyCommand()関数を書き換える

XLPyCommand()関数の中では、.pyファイルを読み込む時のフォルダをPYTHONPATH変数に代入するという処理をしていますが、これもAccessだと動きません。

ですから固定値で代入してあげます。

Function XLPyCommand()
    ～略～

    ' この部分はエラーになるためコメントアウト
    ' If GetDirectoryPath() <> "" Then
    '     PYTHONPATH = GetDirectoryPath() & ";" & GetBaseName(ThisWorkbook.FullName) & ".zip;" & GetConfig("PYTHONPATH")
    ' Else
    '     PYTHONPATH = GetConfig("PYTHONPATH")
    ' End If
    
    ' 読み込んでほしいPythonファイルが置いてあるフォルダを指定
    ' 最後に";"を付ける仕様
    PYTHONPATH = "E:\PyCharmProjects\HelloAccessWings\UseUdf;"

    ～略～
End Function

venvなどの仮想環境で実行したい場合はもうひと手間

また、venvなどの仮想環境で動かしたい場合は、xlwingsモジュールの中にあるDLLロード命令はフルパスで指定するようにします。
パスが通っている場所からしかDLLファイルはロードされないためです。

例えば、

Declare PtrSafe Function XLPyDLLActivateAuto Lib "xlwings32-0.17.1.dll" (ByRef result As Variant, Optional ByVal Config As String = "", Optional ByVal mode As Long = 1) As Long

このような記述の"xlwings32-0.17.1.dll"といった部分を、"E:\PyCharmProjects\HelloAccessWings\venv\xlwings32-0.17.1.dll"といったような絶対パスに書き換えます。

注意

「ファイルが見つかりません: xlwings32-0.17.1.dll」といったエラーになる場合はもう一度DLLファイルがフルパスで書かれているか？確認してみましょう。

AccessからPythonを呼び出してみる

さて、ここまでの手順を終えたらようやくAccessからPythonを呼び出す準備が整いました。

イミディエイトウィンドウで だぉ() 関数を実際に使ってみましょう。

どうでしょうか！
UDF関数として作った だぉ() 関数が正常に動作し、堅苦しい挨拶がフランクに表現されました！

稼働環境にもバージョンの同じxlwingsをインストールする必要あり

今回、Access+xlwingsでPythonを動作させるために、xlwings.basをインポートし、その中身を書き換えるというハックを行いました。

そのため、環境が変わっても、xlwings.basに書いてあるdllファイル名（"xlwings32-0.17.1.dll"）が固定値になってしまい、Accessファイルをコピペした先の環境も同じ名前のdllが入っていることが求められます。

つまり、同じバージョンのxlwingsがインストールされていることが求められます。

邪道ではありますが、開発環境にある"xlwings32-0.17.1.dll"を本番環境のパスが通った場所に置くだけで動作させることができます。

あとがき

今回の内容は、VBAをとりあえず固定値として修正しにいくという、粗削りなものになってしまいました。

粗削りではありますが、シェアしたいと思います。

何故かというと、いくらググってもググっても、Microsoft Accessからxlwingsを使ってPythonを動かすということを説明したページが１つも見つからなかったからです。

粗削りゆえ、うまく動かないなどの質問や、間違っているなどのご指摘がありましたらコメント頂けるとうれしいです。

関連記事:

xlwings入門 MacOS＋PyInstallerで実行ファイル作ろうとしたらハマった DataFrameからAccessデータベースに書き込む DjangoでassertDictEqualを使ってテストをスッキリさせよう

-python

© 2024 ヂまるBlog