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
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])
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.