0

Python code does not run after executing. The code has to read the data frames from the Excel where I run VBA code to execute the python code. Im not sure this could be a reason since I have another totally different excel where I do exactly same running VBA code in excel file to run a python code, which reads the data in this excel file. I get the finished msgbox in less then a second but if I run a code in Pycharm it takes 2 min to finish.

Option Explicit
Sub RunPythonScript()

'Declare Variables
Dim objShell As Object
Dim PythonExe, PythonScript As String

'Create a new Object shell.
Set objShell = VBA.CreateObject("Wscript.Shell")

'Provide file path to Python.exe
'USE TRIPLE QUOTES WHEN FILE PATH CONTAINS SPACES.
PythonExe = """C:\Users\gobro7\AppData\Local\Programs\Python\Python39\python.exe"""
PythonScript = "C:\Users\gobro7\Wholesale - Documents\Report\VL.py"

'Run the Python Script
objShell.Run PythonExe & PythonScript

MsgBox "Finished"

End Sub

Path in python script

import pandas as pd
import numpy as np
import os

# Get the user
username = os.getlogin()

# search for directory
directory = r'C:/Users/' + username + '/Wholesale - Documents/Report/'
Improve this question
7
  • I think you need to add space between PythonExe and PythonScript. Having space in the path may also cause problem if not enclosed in quotes. Also you don't supply optional parameter to bWaitOnReturn, so it will return immediately. Commented Feb 8, 2022 at 20:15
  • Could you explain the last part/ how to add it in my code so it will display msgbox after python script is done ? Im beginner in VBA sorry Commented Feb 8, 2022 at 20:21
  • Check stackoverflow.com/q/8902022/4046632 Commented Feb 8, 2022 at 20:22
  • At which line should I add the space between pythonexe and pythonscript? Commented Feb 8, 2022 at 20:23
  • This is also good reference cpearson.com/excel/ShellAndWait.aspx Commented Feb 8, 2022 at 20:23

1 Answer 1

Reset to default
2

Basically, try

PythonExe = """C:\Users\gobro7\AppData\Local\Programs\Python\Python39\python.exe """
PythonScript = """C:\Users\gobro7\Wholesale - Documents\Report\VL.py"""

'Run the Python Script
objShell.Run PythonExe & PythonScript, 0, True

Note the space at the end of PythonExe as well the triple double-quotes around PythonScript. The 0 will keep the shell window hidden, True will wait for the job to return.

EDIT: Today I was able to test on Windows/Excel. So I created a python script:

import pandas as pd
import time

print('start', str(time.time()))
df = pd.read_excel('Book1.xlsm')
print(df)
time.sleep(10) # just to waste some time like long running operation
print('end', str(time.time()))

and Excel file Book1.xlsm with following macro

Sub RunPythonScript()

'Declare Variables
Dim objShell As Object
Dim PythonExe, PythonScript, cmd As String

'Create a new Object shell.
Set objShell = VBA.CreateObject("Wscript.Shell")

'Provide file path to Python.exe
'USE TRIPLE QUOTES WHEN FILE PATH CONTAINS SPACES.
PythonExe = "C:\Users\SomeRealUser\sandbox\sbox\Scripts\python.exe "
PythonScript = """C:\Users\SomeRealUser\sandbox\py script\mypy.py"""
cmd = PythonExe & PythonScript
Debug.Print cmd
'Run the Python Script
objShell.Run cmd, 1, True

MsgBox "Finished"

End Sub

and some dummy data in Sheet1 and it worked as expected when I run RunPythonScript() macro.

Note:

  1. I use virtual environment named sbox to run the python, with pandas installed - it shouldn't matter or make difference as long as the path is correct and all dependencies are installed.
  2. SomeRealUser is replacement for my real user
  3. In objShell.Run cmd, 1, True I pass as second argument 1 to observe the terminal that is opened, but it works just fine with 0 (i.e. hidden terminal window)
  4. The path to file deliberately has space to mimic your conditions.
  5. All that said - in my opinion there are better ways to accomplish the task of runninf python code or even run the python script with Shell.run (see the link I shared to CPearson)
Improve this answer
Sign up to request clarification or add additional context in comments.

19 Comments

If you find that the imports are not picking up, you may need to set up PYTHONPATH or add the path of the scripts to PATH.
no, its still finishing in 1 sec. I modified data to see the result but there is no change. Python code is not executing
@cup how can i find if imports are picking it up?
If the python script runs in pycharm, there shouldn't be problem with the imports. Try with reading from excel file that is closed.
Did you use the triple quotes for the python executable? You have single quotes in the original posted question even though your comment says use triple quotes
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.