"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?

Is the the VBA module in the xlsm workbook you are trying to open ? — CDP1802, Mar 29 '23 at 17:12
You could create the csv in excel before runnng the python script without the first 2 lines — CDP1802, Mar 30 '23 at 07:56
I couldn't run the vba code from the csv then right? Being able to read a file is not so much the issue, if I do not run the python code from VBA, it works fine. It is that link that fails — Falanpin, Mar 30 '23 at 10:07
What do you mean _run the vba code from the csv_. csv files don't have code. Create the csv using vba in the xlsm rather than in the python script. — CDP1802, Mar 30 '23 at 10:12
I will try to create the csv from the xlsm and to call the python file to open the csv. Do you know of a function that does this? — Falanpin, Mar 30 '23 at 10:27

CDP1802 · Accepted Answer · 2023-03-30T14:23:24.990

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")

edited Mar 30 '23 at 14:23

answered Mar 30 '23 at 14:18

CDP1802

13,871
2
7
17

Thank you for that. I tried it, however, despite having tried to implement a cmd window flashing command of the type `sys.exceptionhook` that calls a function that asks for input, after clicking the buttont that runs the macro, the cmd window flashes by opening and closing immediately.... – Falanpin Mar 31 '23 at 09:54
@Falanpin I tested it with Python 3.11.1. Did the csv get created ? – CDP1802 Mar 31 '23 at 09:56
Yes it did, nonetheless, still the flash – Falanpin Mar 31 '23 at 10:01
@Falanpin Copy the line that was printed in the VBA immediate window into a cmd command prompt. – CDP1802 Mar 31 '23 at 10:07
After a couple of tries and some spaces adding, the command that the VBA outputs does run the python file when copying it in the cmd window, whereas through the button that runs the VBA code it just makes a cmd window flash – Falanpin Mar 31 '23 at 10:32
@Falanpin You could try `.Run "cmd.exe /k " & cmd` but remove the `""` from around the PYEXE string. Are you running my test python ? – CDP1802 Mar 31 '23 at 10:40
I replaced the `"""` by `"`, it does run it, but now gives, by appending a space to the PYEXE path an `Errno 22 Invalid argument` and by not appending the space, a `The system cannot find the path specified.` error – Falanpin Mar 31 '23 at 10:50
Should it be run as so `"PATH"" "` ? – Falanpin Mar 31 '23 at 10:51
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/252896/discussion-between-cdp1802-and-falanpin). – CDP1802 Mar 31 '23 at 10:52

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

1 Answers1