Running Python script directly from Excel

Here is one (admittedly clunky) solution for handling a DataFrame output, as a proof of concept. The code takes a Python script written in the spreadsheet, writes it to "c:\temp\script.py", executes it in a shell and extracts the stdout output to a range on the same sheet.

In a VBA module in the Worksheet:

NB.Include reference to 'Windows Script Host Object Model'

Requires:

The spreadsheet Names 'Script' and 'Output' are defined;

%PYTHONPATH% environment variable is set to the python.exe folder.

Option Explicit

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)

Function processResponse(sh As WshExec) As Variant
    processResponse = CVErr(xlErrNA)

    If sh.Status = WshFailed Then Exit Function
    
    If Not sh.StdErr.AtEndOfStream Then
        processResponse = sh.StdErr.ReadAll()
        Exit Function
    End If

    Dim vRet As Variant
    Dim vLine As Variant
    
    Dim strLine As String
    Dim nRows As Integer
    nRows = 0
    Dim nCols As Integer
    nCols = 0
    
    Dim res As New Collection
    
    While Not sh.StdOut.AtEndOfStream
        strLine = sh.StdOut.ReadLine()
        vLine = Split(strLine, ",")
        res.Add vLine
        If UBound(vLine) + 1 > nCols Then
            nCols = UBound(vLine) + 1
        End If
        nRows = nRows + 1
    Wend
    
    ReDim vRet(1 To nRows, 1 To nCols) As Variant
    
    Dim nRow As Integer
    nRow = 1
    Dim nCol As Integer
    
    Dim r As Variant
    For Each r In res
        For nCol = LBound(r) To UBound(r)
            vRet(nRow, nCol + 1) = r(nCol)
        Next nCol
        
        nRow = nRow + 1
    Next r
    
    processResponse = vRet
End Function

Sub RunScript()
    Dim rngScript As Range
    Set rngScript = Range("Script")
    
    Dim cl As Range
    
    Open "c:\temp\script.py" For Output As #1
    For Each cl In rngScript.Cells
        Print #1, cl.Value
    Next cl
    Print #1, ""
    Close #1
    
    Dim rngOutput As Range
    Set rngOutput = Range("Output")
    
    Dim sh As WshShell
    Set sh = CreateObject("WScript.Shell")

    Dim strPython As String
    strPython = Environ("PYTHONPATH")
    
    Dim strCommand As String
    strCommand = strPython & "\python.exe c:\temp\script.py"
    
    Dim shellExec As WshExec
    Set shellExec = sh.Exec(strCommand)
    
    Do While shellExec.Status = WshRunning
        Sleep 100
    Loop

    Dim vRes As Variant
    vRes = processResponse(shellExec)
    If Not IsArray(vRes) Then
        rngOutput.Value = vRes
        Exit Sub
    End If
    
    Set rngOutput = rngOutput.Resize(UBound(vRes, 1), UBound(vRes, 2))
    rngOutput.Value = vRes
End Sub

The spreadsheet with the script and result:

Clearly you don't need to have the script sitting in the Excel sheet (this is just a demo): you can amend the code to pick up an existing script file. I'm using a CSV output from the script, which can be decoded by VBA to fill a Range with tabular values. Obviously if your output is strings containing ',' then this will cause problems!