Excel VBAからPythonスクリプトを実行する完全ガイド

python -c "import sys; print(sys.executable); print(sys.version)"

python -m venv "C:\Excel Python Demo\.venv"
"C:\Excel Python Demo\.venv\Scripts\python.exe" -c "import sys; print(sys.executable)"

"C:\Excel Python Demo\.venv\Scripts\python.exe" -m pip install pandas matplotlib openpyxl

print("Hello from Python!")

"C:\Excel Python Demo\.venv\Scripts\python.exe" "C:\Excel Python Demo\hello.py"

Option Explicit

Private Const PROJECT_FOLDER As String = "C:\Excel Python Demo"

' この例の固定されたファイルパス専用。自由入力の引数には使わない。
Private Function QuoteFilePath(ByVal value As String) As String
    Dim i As Long

    If Len(value) = 0 Then
        Err.Raise vbObjectError + 1000, , "ファイルパスが空です。"
    End If
    If InStr(value, Chr$(34)) > 0 Or InStr(value, "%") > 0 _
        Or Right$(value, 1) = "\" Then
        Err.Raise vbObjectError + 1001, , "この例では扱えないパスです: " & value
    End If
    For i = 1 To Len(value)
        If AscW(Mid$(value, i, 1)) >= 0 _
            And AscW(Mid$(value, i, 1)) < 32 Then
            Err.Raise vbObjectError + 1002, , "パスに制御文字があります。"
        End If
    Next i
    QuoteFilePath = Chr$(34) & value & Chr$(34)
End Function

Private Sub RequireFile(ByVal path As String)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FileExists(path) Then
        Err.Raise vbObjectError + 1003, , "ファイルがありません: " & path
    End If
End Sub

Public Sub RunPythonAsync()
    On Error GoTo Failed
    Dim pythonExe As String, scriptPath As String, command As String
    Dim taskId As Double

    pythonExe = PROJECT_FOLDER & "\.venv\Scripts\python.exe"
    scriptPath = PROJECT_FOLDER & "\hello.py"
    RequireFile pythonExe
    RequireFile scriptPath
    command = QuoteFilePath(pythonExe) & " " & QuoteFilePath(scriptPath)

    taskId = Shell(command, vbNormalFocus)
    MsgBox "起動要求を行いました。タスクID: " & CStr(taskId) & vbCrLf & _
           "Pythonの完了や成功を確認したわけではありません。"
    Exit Sub
Failed:
    MsgBox "起動できませんでした: " & Err.Description, vbExclamation
End Sub

import argparse
import csv
import logging
from pathlib import Path


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--output", required=True, type=Path)
    parser.add_argument("--log", required=True, type=Path)
    args = parser.parse_args()

    # ログを開く前に検証し、同じファイルの誤上書きを防ぐ。
    if not args.output.is_absolute() or not args.log.is_absolute():
        parser.error("出力とログには絶対パスを指定してください。")
    if args.output.resolve() == args.log.resolve():
        parser.error("出力とログは別のファイルにしてください。")

    # ログの初期化に失敗した場合などは、標準エラーに表示される。
    logging.basicConfig(
        filename=args.log,
        filemode="w",
        encoding="utf-8",
        level=logging.INFO,
        format="%(asctime)s %(levelname)s %(message)s",
    )
    try:
        rows = [("山田", 28), ("佐藤, 花子", 34), ('鈴木 "A"', 29)]
        # 新規ファイルだけを作成し、既存の結果を上書きしない。
        with args.output.open("x", encoding="utf-8-sig", newline="") as f:
            writer = csv.writer(f)
            writer.writerow(["名前", "年齢"])
            writer.writerows(rows)
        logging.info("正常終了: %s件", len(rows))
        return 0
    except Exception:
        logging.exception("処理に失敗しました")
        return 1


if __name__ == "__main__":
    raise SystemExit(main())

Public Sub RunPythonAndImport()
    On Error GoTo Failed
    Dim fso As Object, runner As Object
    Dim pythonExe As String, scriptPath As String, command As String
    Dim jobFolder As String, csvPath As String, logPath As String
    Dim exitCode As Long, message As String
    Dim ws As Worksheet, qt As QueryTable

    Set fso = CreateObject("Scripting.FileSystemObject")
    pythonExe = PROJECT_FOLDER & "\.venv\Scripts\python.exe"
    scriptPath = PROJECT_FOLDER & "\export_result.py"
    RequireFile pythonExe
    RequireFile scriptPath

    jobFolder = fso.BuildPath(fso.GetSpecialFolder(2).Path, fso.GetTempName)
    fso.CreateFolder jobFolder
    csvPath = fso.BuildPath(jobFolder, "output.csv")
    logPath = fso.BuildPath(jobFolder, "run.log")
    command = QuoteFilePath(pythonExe) & " " & QuoteFilePath(scriptPath) & _
              " --output " & QuoteFilePath(csvPath) & _
              " --log " & QuoteFilePath(logPath)

    Set runner = CreateObject("WScript.Shell")
    exitCode = runner.Run(command, 1, True)
    If exitCode <> 0 Then
        Err.Raise vbObjectError + 1004, , _
            "Pythonが失敗しました。終了コード: " & CStr(exitCode)
    End If
    RequireFile csvPath

    Set ws = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    Set qt = ws.QueryTables.Add(Connection:="TEXT;" & csvPath, _
                                Destination:=ws.Range("A1"))
    With qt
        .TextFilePlatform = 65001
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileCommaDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileConsecutiveDelimiter = False
        .TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat)
        If Not .Refresh(BackgroundQuery:=False) Then
            Err.Raise vbObjectError + 1005, , "CSVの取り込みが中止されました。"
        End If
        If .FetchedRowOverflow Then
            Err.Raise vbObjectError + 1006, , "Excelの行数上限を超えています。"
        End If
    End With
    qt.Delete
    Set qt = Nothing

    MsgBox "取り込み完了: " & ws.Name & vbCrLf & _
           "結果とログの保存先: " & jobFolder, vbInformation
    Exit Sub
Failed:
    message = Err.Description
    On Error Resume Next
    If Not qt Is Nothing Then qt.Delete
    On Error GoTo 0
    MsgBox message & vbCrLf & "ログの保存予定先: " & logPath & vbCrLf & _
           "起動前や構文エラーではログがない場合もあります。", vbExclamation
End Sub

from pathlib import Path
import pandas as pd
import matplotlib
matplotlib.use("Agg")  # 画面を開かずファイルへ保存する
import matplotlib.pyplot as plt

base = Path(__file__).resolve().parent
df = pd.read_excel(base / "sample_data.xlsx", engine="openpyxl")
numeric = df.select_dtypes(include="number")
if numeric.empty:
    raise ValueError("集計できる数値データがありません。")

mean_values = numeric.mean()
ax = mean_values.plot(kind="bar", title="Average Values")
output = base / "average_values.png"
try:
    ax.figure.tight_layout()
    # xbは排他的な新規作成。既存の画像がある場合はエラーにする。
    with output.open("xb") as image_file:
        ax.figure.savefig(image_file, format="png")
finally:
    plt.close(ax.figure)