"Errno 13 Permission denied" while running Python Script through VBA

Question

I am tyring to do some data analysis on an .xlsm file through a python script that is opened by VBA although I have checked through the Powershell that my system has the permission requied to open it.

Excerpt of Python file of interest:

import pandas as pd
import csv

df = pd.read_excel('2020 csv prices_less_till_thu called from vba1.xlsm')
df.to_csv('2020 csv prices_less_till_thu called from vba1.csv')

filename = '2020 csv prices_less_till_thu called from vba1.csv'

with open(filename) as csv_file:

Knowing that this could be a permissions error (have tried the solution to this), I checked, using the Powershell that the file was not restricted in its access:

I nonetheless, while running the VBA module:

Set objShell = VBA.CreateObject("Wscript.Shell")
    
    PythonExePath = """C:\Users\[REDACTED]\python.exe """
    PythonScriptPath = """C:\Users\[REDACTED]\Price analysis\Check 2020 from VBA.py"""
    
    objShell.Run PythonExePath & PythonScriptPath

get an Errno13 error:

I've tried the above fix, adding spaces to paths, but can't seem to fix it?

Any idea? Thanks!

PS: Do you know of any library that would be an all in one solution?

Answer 1

Create the csv file in Excel.

Option Explicit

Sub demo()
    Const PYEXE = """C:\Users\[REDACTED]\python.exe"" " 
    Const PY = """C:\Users\[REDACTED]\Price analysis\Check 2020 from VBA.py"" "

    Dim csvname As String, folder As String, cmd

    folder = ThisWorkbook.Path & "\"
    csvname = Replace(ThisWorkbook.Name, ".xlsm", ".csv")
    
    ' create csv copy
    ThisWorkbook.Sheets("Sheet1").Copy
    With ActiveWorkbook
        Application.DisplayAlerts = False ' replace existing
        .SaveAs Filename:=folder & csvname, FileFormat:=xlCSV, _
         CreateBackup:=False
        Application.DisplayAlerts = True
        .Close
    End With

    ' run python script with filename as arg 1
    cmd = PYEXE & PY & Chr(34) & folder & csvname & Chr(34)
    Debug.Print cmd
    With VBA.CreateObject("Wscript.Shell")
        .Run cmd
    End With

End Sub

Python script

import pandas as pd
import sys, csv

csvname = sys.argv[1]
with open(csvname) as csv_file:
    reader = csv.DictReader(csv_file)
    
    for row in reader:
         continue #print(row)
    
print('Opened ' + csvname + "\nLines read {}".format(reader.line_num))
input("Press return to continue")