VBA and python script does not work together

Question

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/'

Answer 1

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)