Call python code in VBA: Permission Error

Question

Im trying to Run the python code from the VBA (Run_report.xlsm).

My xlsm file and all the py files are in the same directory.

Python code has to work on the data which is itself in the Run_report.xlsm file(again this is the file from where i run VBA code to call the python script).

this is the VBA code:

Option Explicit
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\gobro7\AppData\Local\Programs\Python\Python39\python.exe"""
PythonScript = """C:\Users\gobro7\Digital Wholesale - Documents\2. Amazon\Customer Operations_OTC\02. Amazon weekly Report\Amazon weekly automation\VL.py"""

cmd = PythonExe & PythonScript
Debug.Print cmd

'Run the Python Script
objShell.run cmd, 0, True




MsgBox "Finished"

End Sub

and this is the start of the python code to see how I defined the path and reading the files.

import pandas as pd
import numpy as np
import os as os



# Get the user
username = os.getlogin()


# search for directory
directory = r'C:/Users/' + username + '/Digital Wholesale - Documents/2. Amazon/Customer Operations_OTC/02. Amazon weekly Report/Amazon weekly automation/'


# reading SAP extract  files from Run_report.xlsm and creating csv from it

df_1 = pd.read_excel(os.path.join(directory,'Run_report.xlsm'), sheet_name= "weekly",header= None)
df_1 = df_1.drop(df_1.columns[[0,1]], axis=1)
df_1.columns = df_1.loc[3].rename(None)
df_1 = df_1.drop(range(5))

df_1.to_csv(directory + '1.csv', index=False, header= True)


#Read CSV of SAP extract

df_weekly=pd.read_csv(os.path.join(directory,'1.csv'), low_memory=False)

VBA code give me very fast msgbox, and python code is not running.

I checked in CMD and it gave me a PermissionError - because the file is Run_report.xlsm is not closed but I have seen that its possible to Call the python script and do some work in the workbook with pandas even its open. Not sure what Im doing wrong here.

Answer 1

This line 'cmd = PythonExe & PythonScript' produces this string: C:\Users\gobro7\AppData\Local\Programs\Python\Python39\python.exeC:\Users\gobro7\Digital Wholesale - Documents\2. Amazon\Customer Operations_OTC\02. Amazon weekly Report\Amazon weekly automation\VL.py

what you want is: C:\Users\gobro7\AppData\Local\Programs\Python\Python39\python.exe C:\Users\gobro7\Digital Wholesale - Documents\2. Amazon\Customer Operations_OTC\02. Amazon weekly Report\Amazon weekly automation\VL.py

so you need a space between the python command and the location of the python script you are running.

just add a space at the end of the first string:

"""C:\Users\gobro7\AppData\Local\Programs\Python\Python39\python.exe """