excel access denied with win32 python pywin32

Question

My code is

#Opens template for creating final report
excel = win32.dynamic.Dispatch('Excel.Application')
template = os.path.abspath((folderpath+'\Poop.xlsx'))
wb = excel.Workbooks.Open(template)
freshws= wb.Sheets("Fresh") #Sheet names must match perfectly
secws= wb.Sheets("sec")

cur.execute("Select * from FIRALL")
freshdata=list(cur.fetchall())
#writes to the first sheet
datarowlen=0
for i,a in enumerate(freshdata):
    datarowlen = len(a)
    for j,b in enumerate(a):
        freshws.Cells(i+1,j+1).Value = a[j]

cur.execute("Select * from SECVE")
secdata=list(cur.fetchall())
#writes to the second sheet
datarowlen=0
for i,a in enumerate(secdata):
    datarowlen = len(a)
    for j,b in enumerate(a):
        secws.Cells(i+1,j+1).Value = a[j]
#saves the report
wb.SaveAs()
wb.Close()

The error i get when I run my code is

Traceback (most recent call last):
  File "main.py", line 369, in <module>
    wb = excel.Workbooks.Open(template)
  File "<COMObject <unknown>>", line 8, in Open
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel'
, "Microsoft Excel cannot access the file 'path to stuff------------------------
Poop Report\\Poop.xlsx'. There are several possible reasons:\n\n\u2022 The file
name or path does not exist.\n\u2022 The file is being used by another program.\
n\u2022 The workbook you are trying to save has the same name as a currently ope
n workbook.", 'xlmain11.chm', 0, -2146827284), None)

I get a popup dialog saying access is denied. The file isn't readonly and I'm the owner of the workbook its trying to open. I've tried

win32.gencache.EnsureDispatch('Excel.Application')

I still get the same error. Is there something I'm missing? I switched to dynamic thinking late-binding would solve this error.

another error I had was Pywins -2147418111 error when I was trying to fix this code.

Monosymmetric

Where does your folderpath variable come from? Because it looks like you're passing Excel the path 'path to stuff------------------------ Poop Report\\Poop.xlsx', which is obviously not a valid path, adn that's exactly why you're getting that error. – Preclude

I just put that in there to conceal the pathway, the pathways not the issue as I've used openpyxt without problems, however it can't handle pivot tables or update them. – Monosymmetric

Answer 1

I ended up fixing it for some reason this works, if someone could comment why I would appreciate that.

Main thing I changed to open the workbook was the slashes from / to \ in the pathways.

Then I couldn't select the sheet name until I made excel visible.

excel.Visible = True
wb = excel.Workbooks.Open((excelreport+"\Poop.xlsx"))

Oddly enough that got rid of the pywins error

Also changed how sheets are filled its now

cur.execute("Select * from FIRALL")
freshdata=list(cur.fetchall())
#writes to the first sheet
freshws.Range(freshws.Cells(2,1),freshws.Cells((len(freshdata)+1),len(freshdata[0]))).Value = freshdata

Hopefully this helps anyone else who runs into the same issues I did.

Monosymmetric

Even though the excel file contain correct write access, this error could have happened due to slowness. I guess the issue could be due to our Excel application is slow to respond while using win32 module, Before performing SaveAs, made code sleep for 5 seconds helped me fix this issue. – Casandracasanova

Answer 2

A colleague and I were diagnosing this exact issue. I couldn't believe how obscure this was and we found the solution by searching similar issues with .NET equivalent code:

To fix, create a folder called 'Desktop' in 'C:\Windows\SysWOW64\config\systemprofile\' on 64bit architecture or 'C:\Windows\System32\config\systemprofile\' on 32bit servers.

This genuinely fixed an absolutely identical issue.

Grossman

I can't believe this was it. Thank you based SO, thank you Matthew. I don't know whether to laugh or cry now. – Kirkuk

This works, but why? Why TF would Windows have such an random bug? How would these directories come into play? How did you figure this out? – Retrogress

almost 10 years later and I also stumbled to this issue. This time, however, it occurs when I am triggering excel in AWS EC2 via AWS lambda. Thank you for solving this issue, Matthew! I told my colleague and we absolutely lost it – Funny

Answer 3

To solve this issue please follow the below steps. Open DCOMCNFG in RUN, expand Component Services > My Computer > DCOM Config and find Microsoft Excel Application > Properties(right click) -> under the Identity tab set it to "The interactive user for background services.

Junker