1

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)
8
  • Is the PDF document open? Have you tried googling the error? Commented May 29 at 16:10
  • BTW The code starts and closes Excel for every workbook, which is vey slow. Call excel = client.Dispatch("Excel.Application") before the loop and excel.Quit() after it. Same with the alert calls Commented May 29 at 16:15
  • There is no PDF document yet, it's supposed to open each Excel file in the list and then save it as a pdf in the given directory. And yes, I tried googling the error and couldn't find anyone discussing this issue. Commented May 29 at 16:30
  • couldn'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. If export_folder is a relative path, it may not point to the folder you assume. The concatenations may be wrong too. Commented May 30 at 6:46
  • 1
    After seeing the comment from KJ I tried excel.Application.DisplayAlerts = True and 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 using pathlib like you suggested and for some reason, work_sheets.ExportAsFixedFormat(0, final_path) didn't like it, but instead I did work_sheets.SaveAs(final_path, 57) and that seems to work perfectly every time. So Thank you both @PanagiotisKanavos & @KJ. Commented May 30 at 20:34

1 Answer 1

0

Since you didn't specify where you are storing the xlsx files, I placed them in the same folder as the following script.

The first problem I ran into was the call excel.Workbooks.Open I didn't specify the full file path to the xlsx file. I believe that it's needed.

The second problem was that when you used work_sheets.ExportAsFixedFormat you didn't specify a full path name to the exported pdf file.

In the following code I also included the suggestion made by @Panagiotis

Adjust the following for your own export folder.

import os
import win32com.client as client  # installed pywin32 for this to work

def save_pdfs(files_list,export_folder):
    current_directory = os.getcwd()
    excel = client.Dispatch("Excel.Application")

    for line_item, filename in enumerate(files_list, start=1):
        excel.Application.DisplayAlerts = False
        print(line_item, f'{filename}')
        try:
            print(f'{export_folder}\\{filename[:-4]}pdf')
            full_file_path = os.path.join(current_directory, filename)
            wb = excel.Workbooks.Open(full_file_path, ReadOnly=True)
            work_sheets = wb.Worksheets[0]
            work_sheets.ExportAsFixedFormat(0, f'{export_folder}\\{filename[:-4]}pdf')
            wb.Close(False)
        except Exception as e:
            print(f"An error occurred: {e}")

    excel.Application.DisplayAlerts = True
    excel.Quit()

files_list = ["fileone.xlsx", "filetwo.xlsx"]
export_folder = "C:\\Users\\charl\\OneDrive\\Desktop\\PYTHON\\SO_excel_to_pdf\\export"
save_pdfs(files_list,export_folder)
Sign up to request clarification or add additional context in comments.

1 Comment

Hello Charles, I ended up editing my question to provide the full script and the contents of the text file I'm reading to get the file paths.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.