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()
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.