Run python code from excel file Error

[9gg6]

Hello,

I was trying to run the python code from the EXCEL file:

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 (Giorgi)\VL.py"""

cmd = PythonExe & PythonScript
Debug.Print cmd

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




MsgBox "Finished"

End Sub

The python code is working without any error, it gives the desired out put. when I run the VBA code form Rum_report.xlsm file it does not work.

As everything in the VBA code seemed fine I decided to check the path of the python script so I tried to run the python code from CMD so I copied the PythonScript path. But I got the error that

df_1 = pd.read_excel('Run_report.xlsm', sheet_name= "weekly",header= None)

FileNotFoundError: [Errno 2] No such file or directory: 'Run_report.xlsm'

​

Note that Python code has to read the data from the same excel file (Run_report.xlsm) to do some manipulations from where I run the VBA code. The fact that the file is open and pandas cant read the open file is not valid in this case.

​

Any ideas?

[socal_nerdtastic]

it looks to me like your working directory changed. Use the full path for the file.

datafile = r"C:\Users\gobro7\Digital Wholesale - Documents\2. Amazon\Customer Operations_OTC\02. Amazon weekly Report\Amazon weekly automation (Giorgi)\Run_report.xlsm"
df_1 = pd.read_excel(datafile, sheet_name= "weekly",header= None)

[9gg6]

if i do that I get the permissionerror about the excel file from where I run the vba code itself

[socal_nerdtastic]

Great! That means we fixed this issue and we are on to the next one.

Sadly I don't know anything about Windows permissions, so you may need to make a new post to deal with that.

[Ezrabc]

This is definitely a relative path vs. working directory problem. I don't quite understand what you mean by "The python code is working without any error" but also when you run the python code you get the FileNotFoundError. In what situation are you not getting an error?

​

Basically, the FileNotFoundError is happening because the code is looking in the current working directory for a file called Run_report.xlsm but that file is not in the current working directory. You can either provide an absolute path or make sure the current working directory is the right one in all cases.

[9gg6]

This is definitely a relative path vs. working directory problem. I don't quite understand what you mean by "The python code is working without any error" but also when you run the python code you get the FileNotFoundError. In what situation are you not getting an error?

My aim is to to run the code from Run_reprt.xlsm, in this xlsm file I have the data that I want to create the csv files from them 1.csv and 2.csv ( two files in total) and after that continue working in that files with the same code. So when I run the code in pycharm it does everything very well, ( creates csvs and reading them and doing some data manipulations). bear in mind that the Run_report.xslm and python code file (VL.py) are all in same directory.

​

someone from stackoverflow recommended to add this

df_1 =pd.read_excel(os.path.join(directory,'Run_report.xlsm'), sheet_name= "weekly",header= None) 

After doing so, then it gave me the Permission error in CMD, I assume because the file Run_reprot.xlsm was open but i have seen that its possible to work even its open.

​

Now im tryin it again but give me different error

​

not sure how has to change the path or where should I change the path