How to Call Python Script on Excel Vba

How to call python script on excel vba?

Try this:

RetVal = Shell("<full path to python.exe> " & "<full path to your python script>")

Or if the python script is in the same folder as the workbook, then you can try :

RetVal = Shell("<full path to python.exe> " & ActiveWorkBook.Path & "\<python script name>")

All details within <> are to be given. <> - indicates changeable fields

I guess this should work. But then again, if your script is going to call other files which are in different folders, it can cause errors unless your script has properly handled it. Hope it helps.

Run Python script through Excel VBA

You probably need a space between PythonExe & " " & PythonScript. So the command you run is "C:\…\python3.exe" "C:\…\BlackBoxAbsorbers.py" with space between the program path and the python script.

Also note that Dim PythonExe, PythonScript As String only declares PythonScript As String but PythonExe As Variant. In VBA you need to specify a type for every variable: Dim PythonExe As String, PythonScript As String or it is Variant by default!

Further objShell.Run PythonExe & PythonScript is not properly debugable. I recommend to put the command into a variable so you are able to print it in the immediate window for debugging:

Dim Command As String
Command = PythonExe & PythonScript

Debug.Print Command

objShell.Run Command 

If you do this you will see that the output of your command is:

"C:\Users\carlo\AppData\Local\Microsoft\WindowsApps\python3.exe""C:\Users\carlo\Desktop\MSc_Thesis\ML applications\BlackBox\BlackBoxAbsorbers.py"

And there is no space between the 2 strings, where it actually should be:

"C:\Users\carlo\AppData\Local\Microsoft\WindowsApps\python3.exe" "C:\Users\carlo\Desktop\MSc_Thesis\ML applications\BlackBox\BlackBoxAbsorbers.py"

So I recommend:

Public Sub RunPython()    
    Dim objShell As Object
    Set objShell = VBA.CreateObject("Wscript.Shell")

    Dim PythonExe As String
    PythonExe = """C:\Users\carlo\AppData\Local\Microsoft\WindowsApps\python3.exe""" 

    Dim PythonScript As String
    PythonScript = """C:\Users\carlo\Desktop\MSc_Thesis\ML applications\BlackBox\BlackBoxAbsorbers.py"""  

    Dim Command As String
    Command = PythonExe & " " & PythonScript

    objShell.Run Command
End Sub

Problem with calling python script from in excel vba

You need add some space in your shell execution string,
See my example with you code.

Option Explicit

Sub Run_python_script()
' declare the variables
Dim PythonExe, PythonScript As String ' the path of the python file and python script
    'provide the file path to the python exe
    PythonExe = "C:\Users\bbbbbb\AppData\Local\Microsoft\WindowsApps\python.exe"
    'provide the file path to the python script
    PythonScript = "C:\Users\bbbbbb\OneDrive\Bureau\test_python\test.py"
    'run the python script
    Shell PythonExe & " " & PythonScript, vbNormalFocus
End Sub

I fixed your code, tested it and it worked here.
But there is a problem, in excel you don't write to the 'xlsm' file when the file is open in the excel program. So you will not be able to run directly from the same spreadsheet.
I suggest you modify your python code to pass parameters via the command line, such as file name and directory.

Test this code on the terminal first

Your python code:

import pandas as pd
import xlrd 
from openpyxl.reader.excel import load_workbook
from openpyxl import Workbook
file = r'C:\dev\Test.xlsm'
sheetname='Feuil1'
n=2
df = pd.read_excel(file,skiprows=[*range(n)],index_col=[0])

df_static= df[['CF','VPC','CO','MA','status','Project Naming','Poste','family','ressource']]

wb = load_workbook(file, read_only=False, keep_vba=True)
ws = wb.worksheets[0]
year = ws['A1'].value
month = ws['A2'].value

datetime_cols= pd.to_datetime(df.columns,dayfirst=True,errors='coerce')
out = (df.loc[:,(datetime_cols.year == year) & (datetime_cols.month == month)])

df_merge=pd.concat([df_static,out], axis=1)
df_merge

book = wb
writer = pd.ExcelWriter(r'C:\dev\Test.xlsm', engine='openpyxl') 
writer.book = book
writer.vba_archive = book.vba_archive


writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df_merge.to_excel(writer, "ETP",startcol=0,startrow=6)
writer.save()
input("Test")

Is there a way to call a Python code in Excel-VBA?

The simplest way is to run the python interpreter with the Shell command

Shell ("python.exe " & yourScript & " " & arguments)

Execute Python code on clicking in Excel VBA button

Your code should work, I have just tried it myself with no errors. Of course, you will need to return the data later on to see the results!

Alternatively, you could use xlwings. This package allows a lot of interaction between Python and Excel. This is all you would need to write in VBA to run a script (taken from the xlwings documentation RunPython section):

Sub HelloWorld()
    RunPython "import hello; hello.world()"
End Sub

And this is the example code in the hello.py file:

# hello.py
import numpy as np
import xlwings as xw
def world():
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = 'Hello World!'

---

Related Topics