Problem with calling python script from in excel vba

Question

I have a problem calling python scripts from vba in excel, when I run the macro nothing happens!

I don't know exactly where the problem is?

my code in vba:

Option Explicit

Sub Run_python_script()

' declare the variables
Dim objShell As Object

Dim PythonExe, PythonScript As String ' the path of the python file and python script

' create a new shell object
    Set objShell = VBA.CreateObject("Wscript.Shell")
    

    '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
    objShell.Run PythonExe & PythonScript
End Sub

I'm using python 3.7 and the jupyter ide

here is my python code :

import pandas as pd
import xlrd 
   
file = r'C:\Users\bbbbbb\OneDrive\Bureau\test_python\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 = xlrd.open_workbook(file)
ws = wb.sheet_by_name(sheetname)

year = ws.cell(0,0).value
month = ws.cell(1,0).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 = load_workbook(r'C:\Users\bbbbbb\OneDrive\Bureau\test_python\test.xlsm')
writer = pd.ExcelWriter(r'C:\Users\OneDrive\Bureau\test_python\test.xlsm', engine='openpyxl') 
writer.book = book

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

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

writer.save()

my excel sheet 'Feuil1'looks like that :

and when I select the year and the month in cells A1 and A2 , it will generate another sheet called 'ETP' with the selected data.

so by importing the pyrhon script , each time I chose different dates , it will updated automaticaly

thanks in advance for your help

Does your script run? Do you have some way of knowing if it has run? What does/should the script do? — Tim Williams
– Tim Williams, Commented Jan 19, 2021 at 16:15
yes it's run, but then nothing change in the excel workbook ! — Fazia Chenna
– Fazia Chenna, Commented Jan 19, 2021 at 20:05
Does your script make assumptions about the current directory? Things may work differently when called via Shell vs. double-clicking — Tim Williams
– Tim Williams, Commented Jan 19, 2021 at 20:40
I add some information in my question to clarify more the problem — Fazia Chenna
– Fazia Chenna, Commented Jan 19, 2021 at 21:00
Are you attempting to update the same .xlsm workbook that you run the macro? Your Python script can be erring out on a permission issue. Putting aside Excel, does the Python script successfully runs on its own (i.e., command line call in terminal). — Parfait
– Parfait, Commented Jan 19, 2021 at 21:34

jradelmo · Accepted Answer · 2021-01-19 22:19:32Z

2

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")

edited Jan 19, 2021 at 22:19

answered Jan 19, 2021 at 18:27

jradelmo

1262 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Fazia Chenna Over a year ago

i try with your code , but it doesn't work neither, nothing happens!

jradelmo Over a year ago

@FaziaChenna you can provide more information of your operation system, python version and excel version, why in my computer its normal work

Fazia Chenna Over a year ago

I added informations about that in my question

Fazia Chenna Over a year ago

Do you have any suggestions even with xlwings ?

jradelmo Over a year ago

@FaziaChenna i added more informations.. check please.

|

Collectives™ on Stack Overflow

Problem with calling python script from in excel vba

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related