【VBA】VBAからPythonのコードを実行してみる（２）【Python】

VBAにないなら Python のを使えばいいじゃない…の続き

前回は VBA から Python のコードを呼び出して、セルの値を取得→加工→書き込むという処理をみてみました。
今回は os.walk を使ってファイル一覧を取得する処理を実装してみましょう。

celaeno42.hatenablog.com

まずはPythonだけで実行するコード

Python でファイル一覧を取得する処理を記述します。
まずは Python だけで実行できるコードを書いてみましょう。

【xl_getFolderFileList.py】

import os

def getFolderFileList(path):
    for folder, subfolders, files in os.walk(path):
        for file in files:
            filepath = os.path.join(folder, file)
            print(filepath)

getFolderFileList(r'C:\Users\xxxx\Desktop')

実行すると、デスクトップにあるファイルやサブフォルダのファイル一覧を表示します。

さて、これを VBA から実行するように書き換えてみましょう。

VBAから実行する Pythonコード

【xl_getFolderFileList.py】

import os
import xlwings as xw

def getFolderFileList_VBA():
    path = xw.Range('A1').value
    row = 2
    for folder, subfolders, files in os.walk(path):
        for file in files:
            filepath = os.path.join(folder, file)
            rng = 'A' + str(row)
            xw.Range(rng).value = filepath 
            row += 1

まずは、xlwings をインポートして xw で参照できるようにしています。
また、[ path = xw.Range('A1').value ] で、A1セルからパスを取得するようにしています。
ファイルリストの表示は 2行目から行うので、row の初期値に 2 を設定し、os.walk の処理に入ります。
filepath が取得できたら、表示先セルを指定して filepath を表示し、次に備えて row をインクリメントします。

「Pythonだけで実行するコード」と見比べると、Excel のセルとのやり取り部分が増えていますが、基本的な部分は同じことがわかると思います。

では、この Pythonコードを VBA から呼び出す処理を書いてみましょう。

VBA から Python を呼び出すコード

【VBA - 標準モジュール】

Public Sub getFolderFileList()
    Call RunPython("import xl_getFolderFileList; xl_getFolderFileList.getFolderFileList_VBA()")
End Sub

書き方は前回のコードと同様です。
ファイル名と呼び出すプロシージャ名が変わったくらいですね。

では、A1セルに対象フォルダのパスを記述して実行してみましょう。
今回はデスクトップに作った「無限不可能性ドライブ」フォルダを対象にしてみました。
実行結果はこのようになります。
f:id:celaeno42:20181127235752p:plain

サブフォルダまで検索したファイル一覧が作成されました。

さて、今回のサンプルでは、対象となるフォルダはA1セルに設定されたパスのフォルダのみです。
これを例えば、A列にはA1セルに設定されたパスのフォルダ、B列にはB1セルに設定されたパスのフォルダ…
というようにするにはどうしたらよいでしょう。

VBAの呼び出し側で Pythonの関数の引数となる値を渡してあげればよさそうです。

引数を受け取る Pythonコード

今回は引数として Excel の列番号（col）を受け取るようにしてみました。
前回のコードとの違いは Range() のセルの指定部分だけです。
今回は行番号と列番号で指定しています。
かっこが２重になっている（タプルになっている）ことに注意してください。

【xl_getFolderFileList.py】

import os
import xlwings as xw

def getFolderFileList_VBA2(col):
    path = xw.Range((1, col)).value
    row = 2
    for folder, subfolders, files in os.walk(path):
        for file in files:
            filepath = os.path.join(folder, file)
            xw.Range((row, col)).value = filepath
            row += 1

では、VBAから呼び出してみましょう。

VBA から Python を呼び出すコード（引数あり）

【VBA - 標準モジュール】

Public Sub getList()
    Call getFolderFileList2(1)
    Call getFolderFileList2(2)
End Sub

Private Sub getFolderFileList2(ByRef col As Long)
    Call RunPython("import xl_getFolderFileList; xl_getFolderFileList.getFolderFileList_VBA2(" & col & ")")
End Sub

引数の指定部分は、まぁ、よくある文字列との連結の書き方ですね。

では、A1セル、B1セルに対象フォルダのパスを記述し、「getList()」プロシージャを実行してみましょう。
A1セルには先ほどと同じフォルダ、B1セルには「Documents」フォルダ内の「vba」フォルダを指定しています。

実行結果はこのようになりました。
f:id:celaeno42:20181128231708p:plain
A列とB列でファイルの一覧が取得されています。

まとめ

サブフォルダ内も含むファイル一覧の取得も、Python の関数を使うことで比較的簡単に実現できたのではないでしょうか。
他にも Python でやったほうが簡単な処理があると思いますので、VBAだとちょっと…という場合には、このような方法も選択肢としてはありかなと思います。

ただし…わざわざ Python を呼び出しているためか、VBAのみで記述するよりは遅いのでまぁ、それを許容できるかというのもあるかもしれませんね。

おまけ

Excelとの直接の連携を考えないなら単純にこのような方法もあります。
コマンドプロンプトから [ >python ファイル名.py ] で実行するのと同じなので、
xlwings や pywin32 は不要です。

Pythonのコードはメッセージボックスを表示するだけの簡単なものです。

【helloworld.py】

from tkinter import messagebox

messagebox.showinfo('vba2py', 'Hello World! from VBA')

【VBA - 標準モジュール】

Option Explicit

Public Sub pythonコードを実行()
    Dim obj As Object
    Dim pyPath As String
    
    Set obj = CreateObject("WScript.Shell")
    
    'Pythonファイルのパスはフルパスを設定するようにしたほうがいいかも
    pyPath = "C:\Users\xxxx\xxxxxxxx\helloworld.py"

    Call obj.Run("Python " & pyPath, WaitOnReturn:=True)
    
    Set obj = Nothing
    
End Sub

【実行結果】
f:id:celaeno42:20181124222122p:plain

※※　ノンプロ研アドベントカレンダー7日目の記事です　※※
adventar.org