Python: Is there way how to write in to Excel Cell and export PDF in 1 loop

Question

I do have sticky situation with my project. I am trying to update Excel Sheet and export it to PDF in one loop. At moment I bevies’ best for this is openpyxl library. Issue is that both are functions writing and printing are opening Excel different way.. using:

book = openpyxl.load_workbook(excel_file) and wb = excel.Workbooks.Open(excel_file).

Both functions are crossing each other and creating permission issues (at least it is looking like it) plus crashing Jupyter :).

PLEASE is there any elegant way how to do this or I really need 2 loops?

Error call example: PermissionError: [Errno 13] Permission denied: 'C:/Users/admin/test_files/dir#$$.xlsx'

Code is looking like this:

def update_directory():


excel_file = r'C:/Users/admin/test_files/doo.xlsx'

excel = client.DispatchEx("Excel.Application")
excel.Visible = 0

folder_selected = filedialog.askdirectory()
os.chdir(folder_selected)


for root, dirs, files in os.walk(".", topdown=False):
    
    for name in dirs:
                                               
        a_pth = os.getcwd()
        pdf_file = os.path.join(a_pth,name," ")+"Dic_"+"%s.pdf" % name
            
        book = openpyxl.load_workbook(excel_file)
        sheet= book['Sheet1']
        sheet.cell(row=4, column=6).value = name
            
        book.save(excel_file)
            
        wb = excel.Workbooks.Open(excel_file)
        ws = wb.Worksheets[1]
         
        ws.SaveAs(pdf_file, FileFormat=57)
            
            
wb.Close() # <- need to be part of loop (comment from Amiga500). File save 
           #    prompt from Excell present. 
excel.Exit()

**wb**.SaveAs(pdf_file, FileFormat=57) ? Not sure if you can save a worksheet as a pdf on its own...? This link is probably of more use than my ramblings... https://stackoverflow.com/questions/52326782/python-converting-xlsx-to-pdf — Amiga500, Apr 20 '21 at 08:50
Hi :) thanks for message I was looking in that post previously and lot of good stuff there. to answer your question you can do it and in my example it is working just when you iterate to another loop you have issue. — Jan M., Apr 20 '21 at 14:36
Ah - well in that case are you not missing a book.close() and should wb.close() not be nested up just after ws.SaveAs() ? [BTW - as presented above, you are trying to open a path, not a file with load_workbook(excel_file)] — Amiga500, Apr 20 '21 at 14:55
Uff great catch it is mine mistake during adjusting code for SO I deleted xlsx filename it would definitely close PermissionError. I am sucking that directory from previous part of program by filedialog.askopenfilename() but I have double checked <- good eyes. I am testing that second book.close() -> it is going with some success doing what it should but for some reason always asking if I want to save changed excel. — Jan M., Apr 20 '21 at 19:30
Ah, I see its wb.close() prompting a save - if that is within excel, then you need to disable alerts, "application.DisplayAlerts = false" - how you hook that in to your code I'm not so sure. I'll have a look. — Amiga500, Apr 21 '21 at 07:57
"wb.application.displayalerts = False" seems to have worked for me — Amiga500, Apr 21 '21 at 08:17
....So tested and is working..... !!!! Thanks much again !!!! — Jan M., Apr 21 '21 at 13:53

score 0 · Accepted Answer · answered Apr 21 '21 at 08:20

Having an entry

wb.application.displayalerts = False

Inserted just before the

wb.Close()

line seems to have worked for me, so the code snippet would resemble

    book = openpyxl.load_workbook(excel_file)
    sheet= book['Sheet1']
    sheet.cell(row=4, column=6).value = name
        
    book.save(excel_file)
        
    wb = excel.Workbooks.Open(excel_file)
    ws = wb.Worksheets[1]
     
    ws.SaveAs(pdf_file, FileFormat=57)
    
    wb.application.displayalerts = False  #This stops the popup asking for a save
    wb.Close() # <- need to be part of loop (comment from Amiga500). File save 
       #    prompt from Excell present.

Note wb.Close() is at same indentation as the rest of inner for loop.

Python: Is there way how to write in to Excel Cell and export PDF in 1 loop

1 Answers1