Capture values returned by Python into a VBA Script

Question

I have this code on my vba:

Sub RunPythonScript()

Dim objShell As Object
Dim PythonExe, PythonScript As String

Set objShell = VBA.CreateObject("Wscript.Shell")

PythonExe = """C:\Program Files\Python37\python.exe"""
PythonScriptPath = "C:\Users\pma\PycharmProjects\XRef\testing.py"

objShell.Run PythonExe & PythonScript

End Sub

My python script returns a dataframe:

return df

How do I capture this in my VBA? I want to play around with the returned data frame

https://stackoverflow.com/questions/39516875/return-result-from-python-to-vba/39517658 shows how to read from `StdOut`, but this would not help you pass a dataframe. I'm not a Python user but I suspect a dataframe would not be usable within a VBA environment. — Tim Williams, Jul 07 '21 at 20:40
You are running two separate programs. You can pass the data by printing in python and reading from VBA. Other alternatives are printing to and reading from a file or writing to a socket and reading from it. — cup, Jul 07 '21 at 22:01

Алексей Р · Answer 1 · 2021-07-09T15:52:00.650

Options include transferring data via the clipboard or via a file, as @cup suggested:

VBA part (with option selection):

Sub RunPythonScriptByFileOrClip()
    Dim objShell As Object
    Dim PythonExe As String, PythonScriptPath As String, Opt As String
    
    Set objShell = VBA.CreateObject("Wscript.Shell")
    
    PythonExe = """C:\Program Files\Python37\python.exe"""
    PythonScriptPath = "C:\Users\pma\PycharmProjects\XRef\testing.py"
    Filename = "C:\test\df.xlsx"
    Opt = "-byclip" ' or "" for byfile
    
    If Opt = "-byclip" Then
        objShell.Run Join(Array(PythonExe, PythonScriptPath, Opt), " "), 0, True  'Run(<Command>,<WindowStyle>,<WaitOnReturn>)
        ' create a new worksheet in ThisWorkbook for paste df
        On Error Resume Next
        Application.DisplayAlerts = False
        ThisWorkbook.Sheets("df").Delete    'delete old df sheet
        On Error GoTo 0
        Application.DisplayAlerts = True
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "df"
        ws.Paste ws.Range("A1")
        Set df = ws.UsedRange
    Else  ' via file
        objShell.Run Join(Array(PythonExe, PythonScriptPath, Filename), " "), 0, True  'Run(<Command>,<WindowStyle>,<WaitOnReturn>)
        Set wb = Workbooks.Open(Filename)
        Set df = wb.Sheets(1).UsedRange
    End If
    ' process the data in any way
    For Each cl In df
        Debug.Print cl.Text
    Next
End Sub

Python part:

import pandas as pd
import sys

df = pd.DataFrame({'column1': [1, 3, 4, 5], 'column2': [9, 8, 7, 6]})
if len(sys.argv) == 2:
    if sys.argv[1] == '-byclip':
        df.to_clipboard()
    else:
        df.to_excel(sys.argv[1])

Capture values returned by Python into a VBA Script

1 Answers1