I'm trying to run a script that reads a txt file that contains a list of file paths to Excel files, and save each one as a pdf in a specified directory. I can get it to read the txt file and parse the list of file paths no problem, but it runs into an error when it tries to save the file as a pdf.
Here's my save pdfs function that runs into an error:
def save_pdfs(files_list,export_folder):
for line_item, filename in enumerate(files_list, start=1):
excel = client.Dispatch("Excel.Application")
excel.Application.DisplayAlerts = False
print(line_item, f'{filename}')
try:
wb = excel.Workbooks.Open(filename, ReadOnly=True)
work_sheets = wb.Worksheets[0]
if len(str(line_item)) == 1:
work_sheets.ExportAsFixedFormat(0, f'{export_folder}\\0{line_item}')
else:
work_sheets.ExportAsFixedFormat(0, f'{export_folder}\\{line_item}')
except Exception as e:
print(f"An error occurred: {e}")
finally:
wb.Close(False)
excel.Application.DisplayAlerts = True
excel.Quit()
And whenever I run this, I get the following error for each of the files:
An error occurred: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Document not saved. The document may be open, or an error may have been encountered when saving.', 'xlmain11.chm', 0, -2146827284), None)
I don't have Excel running when I start the script and I've checked the files themselves and nobody else has the file open already. If anyone else has run into a similar situation, please let me know what you did to fix it.
Edit: Here's the full script, and the file it asks for is a txt file that consists of the full file paths to each individual file, since they're mostly in different directories. I populated my test file by selecting the files, right clicking, and selecting "Copy as path" and pasting into the text file.
I also did make the changes that Panagiotis Kanavos suggested about moving the opening and quitting of Excel outside of the loop, thank you for the advice!
from win32com import client
from tkinter import filedialog, messagebox
from pypdf import PdfWriter
import glob
def pdf_merge(directory):
merger = PdfWriter()
pdf_file_list = glob.glob(f'{directory}\\*.pdf')
for pdf in pdf_file_list:
merger.append(pdf)
merger.write(f'{directory}\\Merged.pdf')
merger.close()
def read_filenames(filepath):
filenames = []
try:
with open(filepath, 'r') as file:
for line in file:
filename = line.strip().replace('"','')
if filename:
filenames.append(filename)
except FileNotFoundError:
print(f"Error: File not found at path: {filepath}")
except Exception as e:
print(f"An error occurred: {e}")
return filenames
def save_pdfs(files_list,export_folder):
excel = client.Dispatch("Excel.Application")
excel.Application.DisplayAlerts = False
for line_item, filename in enumerate(files_list, start=1):
print(line_item, f'{filename}')
try:
wb = excel.Workbooks.Open(filename, ReadOnly=True)
work_sheets = wb.Worksheets[0]
if len(str(line_item)) == 1:
work_sheets.ExportAsFixedFormat(0, f'{export_folder}\\0{line_item}.pdf')
else:
work_sheets.ExportAsFixedFormat(0, f'{export_folder}\\{line_item}.pdf')
except Exception as e:
print(f"An error occurred: {e}")
finally:
wb.Close(False)
excel.Application.DisplayAlerts = True
excel.Quit()
messagebox.showinfo(title='Seleect File', message= 'Please select text file with listed file paths.')
file_path = filedialog.askopenfilename()
files_list = read_filenames(file_path)
messagebox.showinfo(title='Seleect Folder', message= 'Please select folder to save output files.')
export_folder = filedialog.askdirectory()
save_pdfs(files_list, export_folder)
if messagebox.askyesno(title="Merge?",message="Do you want to merge the PDFs?"):
pdf_merge(export_folder)
For testing purposes, the text file's contents look like this:
"C:\Python Scripts\Test Files\Test File 1.xlsx"
"C:\Python Scripts\Test Files\Test File 2.xlsx"
"C:\Python Scripts\Test Files\Test File 3.xlsx"
"C:\Python Scripts\Test Files\Test File 4.xlsx"
But in practice will be a list of file paths in different directories on the network.
As a side note, everything seems to be fine with opening the Excel files, I tested it with commenting out the part where it closes each file and quits Excel and each file opens successfully, it's just an issue with it then trying to save the files as PDFs.
---Second Edit---
I was able to get it working using pathlib like Panagiotis Kanavos suggested. Here's the fully working script that I have going:
from win32com import client
from tkinter import filedialog, messagebox
from pypdf import PdfWriter
from pathlib import Path
import glob
def pdf_merge(directory):
merger = PdfWriter()
pdf_file_list = glob.glob(f'{directory}\\*.pdf')
for pdf in pdf_file_list:
merger.append(pdf)
merger.write(f'{directory}\\Merged.pdf')
merger.close()
def read_filenames(filepath):
filenames = []
try:
with open(filepath, 'r') as file:
for line in file:
filename = line.strip().replace('"','')
if filename:
filenames.append(filename)
except FileNotFoundError:
print(f"Error: File not found at path: {filepath}")
except Exception as e:
print(f"An error occurred: {e}")
return filenames
def save_pdfs(files_list,export_folder):
excel = client.Dispatch("Excel.Application")
excel.Visible = False
excel.Application.DisplayAlerts = False
for line_item, source_file_path in enumerate(files_list, start=1):
print(line_item, f'{source_file_path}')
final_path = export_folder / (str(line_item).zfill(2) + '.pdf')
print(final_path)
try:
wb = excel.Workbooks.Open(source_file_path, ReadOnly=True)
work_sheets = wb.Worksheets[0]
work_sheets.SaveAs(final_path, 57)
except Exception as e:
print(f"An error occurred: {e}")
finally:
wb.Close(False)
excel.Application.DisplayAlerts = True
excel.Visible = True
excel.Quit()
messagebox.showinfo(title='Seleect File', message= 'Please select text file with listed file paths.')
file_path = Path(filedialog.askopenfilename())
#print(file_path)
files_list = read_filenames(file_path)
#print(files_list)
messagebox.showinfo(title='Seleect Folder', message= 'Please select folder to save output files.')
export_folder = Path(filedialog.askdirectory())
#print(export_folder)
save_pdfs(files_list, export_folder)
if messagebox.askyesno(title="Merge?",message="Do you want to merge the PDFs?"):
pdf_merge(export_folder)
excel = client.Dispatch("Excel.Application")before the loop andexcel.Quit()after it. Same with the alert callscouldn't find anyone discussing this issue.on the contrary there are a lot of results. They may not look relevant because they don't talk about Python, but the error comes from Excel, during normal operation. You'd probably get the same error from Excel, assuming the paths are correct. You didn't post them, so we can't guess. Ifexport_folderis a relative path, it may not point to the folder you assume. The concatenations may be wrong too.excel.Application.DisplayAlerts = Trueand nothing was popping up as a dialogue box when it opened the file, and I was able to manually save the file as a PDF without any issues popping up as well. That said, I did end up usingpathliblike you suggested and for some reason,work_sheets.ExportAsFixedFormat(0, final_path)didn't like it, but instead I didwork_sheets.SaveAs(final_path, 57)and that seems to work perfectly every time. So Thank you both @PanagiotisKanavos & @KJ.