VBAでPython(pyファイル)を実行する方法2｜値を双方向で受け渡し合うには

以前「VBAでPython(pyファイル)を実行する方法」でコマンドプロントを用いてVBAからPythonコードを実行する方法を紹介しました。

ただ、この方法はpyファイルを実行しているだけで、その結果をVBAに返すことはできません。
やはりVBAで実行したからには最終的にVBAに値を返し、VBA内でプログラムを終了させたいです。

また、以前の方法ではPythonに渡す引数をVBAとPythonの両コードで指定する必要がありました。
つまり引数として10個の値を渡したい場合は、VBAで受け渡し用のコードを10個分、Pythonで受け取り用のコードを10個分書く必要がありました。これは非常に面倒ですし、引数の数が変わった場合も毎回VBAとPythonの両コードを書き直さないといけないため、もう少し使い勝手の良いものにしたいです。

そこで、今回は中間ファイルとしてテキストファイルを作成し、VBAとPythonの値を双方向で受け渡し合う方法を紹介していきます。この方法を使うことで引数の数は関係なくVBAからPython、PythonからVBAのようにどちら側からでも値を受け渡すことが可能になります。

簡単に言えばPythonコードをVBAの関数のように扱うことができるというわけです。

注意

本ページは前回「VBAでPython(pyファイル)を実行する方法」の続きの内容です。
前回のページの内容を理解している前提の内容になっているので、まだ前回のページを読んでいない方は先にそちらから読んでいただくことをオススメします。

サブスクでプログラミング学習してみよう！

Pythonをはじめいくつものプログラミング言語の学習が可能！
通常なら月10,000円以上はかかるプログラミング学習が約3000円という国内屈指の安さ！
サブスク型なのでいつでも始められて、いつでも辞められるという気軽さ！

▼▼▼ 詳しくは下バナーをクリック ▼▼▼

Contents

1. 中間ファイルの作成
- 1.1. VBAでのテキストファイルの操作
- 1.2. Pythonでのテキストファイルの操作
2. サンプルコード
3. まとめ

中間ファイルの作成

基本的にどのプログラミング言語でもテキストファイルを作成したり、書き込みや読み取りなどの操作はできます。そこで、どの言語からでもアクセス可能なテキストファイルを"中間ファイル"として作成し、VBAとPython間での値の受け渡しに利用します。

ここでは、VBAとPythonそれぞれのコードでのテキストファイルの作成、書き込み、読み取りの方法を簡単に解説していきます。(より詳しく知りたい方は「VBA テキストファイル」「Python テキストファイル」等で検索してみてください)

VBAとPythonのサンプルコードはページ最後に載せているので、テキストファイルの操作説明が不要の方は目次より「サンプルコード」の項へ移動してください。

VBAでのテキストファイルの操作

VBAでファイルの取り扱いを行う際には「FileSystemObjectオブジェクト」が必要です。
まずは以下のように書いてオブジェクトの定義をしておきます。

FileSystemObjectオブジェクトの定義

Dim fso As FileSystemObject
Set fso = CreateObject(“Scripting.FileSystemObject")

上記コードにより変数「fso」を使ってテキストファイルの操作を行うことが可能になります。

　
テキストファイルの作成

テキストファイルを作成するには「FileSystemObjectオブジェクト」の「CreateTextFileメソッド」を使って以下のように書きます。

CreateTextFileメソッド

fso.CreateTextFile (フルパス)

フルパスには作成するテキストファイルのフルパスを入力します。
例えばデスクトップ上の「VBA」というフォルダ内に「tmpvalue.txt」というテキストファイルを作成する場合、フルパスには「"C:\Users\ユーザー名\Desktop\VBA\tmpvalue.txt"」という文字列を入力します。

このとき、同フォルダに同名ファイルの存在を確認するには同オブジェクトの「FileExistsメソッド」を使います。同名のファイルが存在する場合に、新規テキストファイルを作成しないようにするには以下のような条件文を追加します。

FileExistsメソッド

If Not fso.FileExists(フルパス) Then

　　fso.CreateTextFile (フルパス)

End If

テキストファイルに書き込み

テキストファイルに値を書き込むには、手動での書き込みと同じような考えで、
① テキストファイルを開く
② 開いたテキストファイルに値を書き込み
③ テキストファイルを閉じる
といった処理の流れになります。(手動とは違いファイルを開いた時に画面には表示されません)

テキストへの書き込みはほとんど定型文で、以下のように書きます。

テキストファイルに書き込み

Dim FileNo As Integer
FileNo = FreeFile
Open フルパス For Output As #FileNo '①の処理
Print #FileNo, “書き込む値"　　　　　　'②の処理
Close #FileNo '③の処理

テキストファイルの読み取り

テキストファイルの値を読み取るには、書き込みと同じくテキストファイルを開く必要があります。
このとき「Line Inputステートメント」を使うことで、テキストファイルに書かれている値を1行ごとに取得することができます。

読み取りもほぼ定型文で以下のように書きます。
(以下コードの場合、イミディエイトウィンドウに1行ずつ文字列が書き出されます)

テキストファイルの読み取り

Dim FileNo As Integer
FileNo = FreeFile

Dim data
Open フルパス For Input As #FileNo
Do Until EOF(1)
　　　Line Input #FileNo, data
　　　Debug.Print data　'data(1行毎の文字列)を使った処理
Loop

Close #FileNo

「Line Inputステートメント」は「ｉ」などのカウント変数を使う必要がなく、ループ毎に取得する行が更新されていくので注意が必要です。

Pythonでのテキストファイルの操作

Pythonでのテキストファイルの操作は非常にシンプルで、VBAのように特定のオブジェクトを定義する必要はありません。また、今回の内容ではPythonでテキストファイルを作成することはないので、この項では「書き込み」と「読み込み」の方法だけ簡単に説明していきます。

　
テキストファイルの読み取り

Pythonでテキストファイルに値を読み取るには以下のように書きます。
フルパスには値を読み取るテキストファイルのフルパスを入力します。

テキストファイルに読み取り

f = open(フルパス)
Datas = f.readlines() #テキストファイルの値をdatasに格納
f.close

「f.read()」でも値を取得することはできますが、今回は1行ずつの値を取得したいので「f.readlines()」を使います。上記コードによりテキストファイルの値を1行ずつDatasというリスト(配列)に格納します。
　

テキストファイルに書き込み

Pythonでテキストファイルに値を書き込むには以下のように書きます。
フルパスには値を書き込むテキストファイルのフルパスを入力します。

テキストファイルに書き込み

f = open(フルパス, “w")
for Data in Datas:
f.write(Data) #Dataをテキストファイルに上書き
f.close()

上記コードの場合、Datasというリストに入っている値が１つずつテキストに書き出されていきます。

サンプルコード

最後にこれまでの内容をまとめたサンプルコードを紹介します。
処理の流れは以下のとおりです。

⓪ VBA実行
① tmpファイル作成(pyファイルと同じフォルダ内)
② tmpファイルにVBA内の値を書き込み
③ tmpファイルのフルパスを引数にpyファイルを実行
④ pyファイル実行
⑤ tmpファイルのフルパスを表示(確認用)
⑥ tmpファイルの値を1行毎に読み取り
⑦ tmpファイルの値に「 Python処理済み」という文字列を追加　　　　　　　　
⑧「Python処理完了」というメッセージを表示(確認用)
⑨ tmpファイルの値を1行毎に読み取り
⑩ イミディエイトウィンドウに値を表示
　
※ 青文字 = Python処理

VBAでは「コード内で指定した値をPythonに送り、Pythonの結果を受け取ってイミディエイトウィンドウに表示する」という処理、Pythonでは「VBAから受け取った値に「 Python処理済み」という文字列を追加する」という処理を行います。
(例えばVBAで「ABC」という文字列を指定して実行すると、「ABC Python処理済み」という文字列がミディエイトウィンドウに表示される)

今回はサンプルコードということでVBAコード内でPythonに送る値を指定していますが、コードの内容さえ理解できればセルの値を取得して送ることも可能になります。

pyファイル準備

まずpyファイル(Pythonのコード)を作成します。
VBAから実行された際に、Python内でエラーが発生すると特に何も表示されずにpyファイルの処理が中断されます。そのため、Pythonでエラーが出ていないことを確認するためにpyファイルの実行時と終了時にメッセージを表示するようにしています。
コードの内容が理解できたらメッセージ表示の処理は削除しても問題ありません。

import tkinter as tk
from tkinter import messagebox
import sys

def function(path):
	root = tk.Tk()
	root.withdraw()

	title = "実行確認"
	msg = "tmpファイル : " + path
	messagebox.showinfo(title, msg)
    
	f = open(path)
	Datas = f.readlines()		#VBAからの値をdatasに格納
	f.close
	
	#VBAからの値を使ったPython処理*************************
	
	PyDatas = []
	for Data in Datas:
		x = Data + " Python処理済み"
		x = x.replace("\n","")	#元あった改行コードを削除
		x = x + "\n"			#要素の最後に改行コードを追加
		PyDatas.append(x)
	
	#******************************************************
	
	f = open(path, "w")
	for PyData in PyDatas:
		f.write(PyData)			#VBAに渡す値をtmpファイルに上書き
	f.close()
	
	msg = "Python処理完了"
	messagebox.showinfo(title, msg)
	
if __name__ == "__main__":
	argv = sys.argv				#コマンドライン引数を扱うための変数argvを定義
	path = str(argv[1])			#引数を文字列としてpathに代入

	function(path)

VBAコード作成

コマンドプロンプトを使ってpyファイルを実行するという処理をVBAで書いていきます。
以前のサンプルコードではコマンドプロンプトの実行として「Execメソッド」を使っていましたが、今回は「Runメソッド」を使用します。これはコマンドプロンプトの処理が終わるまでVBAの処理を待つという機能を利用するためです。(引数 Trueの部分)

モジュールを1つ作成し以下のコードをコピペして下さい。
※py_pathにはpyファイルの保存場所のパス
　py_nameにはpyファイルのファイル名
　tmp_nameには中間ファイルのファイル名をそれぞれ入力して下さい。

Option Explicit
Sub Run_Python()

    Dim wsh As WshShell
    Set wsh = CreateObject("WScript.Shell")
    
    Dim fso As FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    
  'Python(pyファイル)パス指定
    Dim py_path As String
    py_path = "C:\Users\ユーザー名\Desktop\VBA"     'pyファイル保存場所
    
    Dim py_name As String
    py_name = "sample.py"                       'pyファイル名
    
    Dim py_file As String
    py_file = py_path & "\" & py_name           '"'pyファイル フルパス作成 
    
    
  'tmpファイル(中間ファイル)指定                                                                        '"
    Dim tmp_name As String
    tmp_name = "tmpvalue.txt"                   'tmpファイル名                                        '"
    
    Dim tmp_file As String
    tmp_file = py_path & "\" & tmp_name         '"'tmpファイル フルパス作成
    
    If Not fso.FileExists(tmp_file) Then
        fso.CreateTextFile (tmp_file)           'tmpファイルが存在しない場合は作成                        '"
    End If
      
      
  'Pythonに渡す値(引数)をtmpファイルに書き込み
    Dim FileNo As Integer
    FileNo = FreeFile
    
    Dim ArgArray
    ArgArray = Array("ABC", 123, 4.56)           'Pythonに送る値を指定
    
    Open tmp_file For Output As #FileNo
        Dim i As Integer
        For i = 0 To UBound(ArgArray)
            Print #FileNo, Trim(ArgArray(i))
        Next i
    Close #FileNo
    
    Dim path As String
    path = tmp_file
    
    Dim cmd_str As String
    cmd_str = "python " & py_file & " " & path   '実行コードの作成
                                                 '引数にtmpファイルのフルパスを設定
                                                 
  'Python(pyファイル)実行
    Call wsh.Run(cmd_str, 0, True)
    
    
  'Python戻り値(tmpファイル)の取得
    Dim tmp_data
    Dim tmp_row As Integer
    
    Set tmp_data = fso.OpenTextFile(tmp_file, 8)
    tmp_row = tmp_data.line
    tmp_data.Close

    Dim PyDatas()
    ReDim PyDatas(tmp_row - 2)
    Dim data
    Open tmp_file For Input As #FileNo
    i = 0
    Do Until EOF(1)
        Line Input #FileNo, data
        PyDatas(i) = data
        i = i + 1
    Loop
    Close #FileNo
    
    'Kill tmp_file      tmpファイル削除用
    
    Set wsh = Nothing
    Set fso = Nothing
    
    
  '取得確認
    For i = 0 To UBound(PyDatas)
        Debug.Print PyDatas(i)
    Next i

End Sub

※コード内にある「'"」はブラウザ表示の際に色がおかしくなるのを防ぐためのものです。
　コード自体には全くの無関係なので気になる方は削除してください。

最終的にPythonから受け取った値は「PyDatas」という配列に全て格納されます。
本コードでは取得確認としてイミディエイトウィンドウに表示という処理で終わらせています。

また、pyファイルによって上書きされたテキストファイルは確認用として残しています。
コード実行毎に削除したい場合は「’Kill tmp_file」のコメント化を解除してください。