Click here to Skip to main content
14,577,943 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have written a program to automate the creation of pdf invoices for Amazon orders. Amazon creates an excel workbook with all the information needed for the invoice. My code extracts the data, then appends to it product description and other stuff from a separate excel workbook and sends it to a predefined template in a third workbook. This workbook is then exported as pdf and sent to the individual clients.

Here is the full thing for reference but the problem is in Block 3, line formXl = excel.Workbooks.Open("path").

import openpyxl as xl # Manipulate Form
from datetime import date # Imports Date module
import win32com.client # Open excel save as pdf
from pywintypes import com_error # Show error if something wrong in conversion

#-------------------------------------------------------------------------------
# BLOCK 1: IMPORTS ALL THE WORKBOOKS NEEDED TO INPUT AND OUTPUT DATA

# Import the Amazon and descriptions spreadsheets with openpyxl
workbook = xl.load_workbook(r"C:\Users\t_man\Documents\ThatsArte Fatture XL\Descrizioni.xlsx", read_only=True, data_only=True)
descriptions = workbook.active
workbook = xl.load_workbook(r"C:\Users\t_man\Documents\ThatsArte Fatture XL\Amazon ordini 2020.xlsx", read_only=True, data_only=True)
fatture = workbook.active
workbook = xl.load_workbook(r"C:\Users\t_man\Documents\ThatsArte Fatture XL\Output Form.xlsx", read_only=False, data_only=True)
form = workbook.active

#-------------------------------------------------------------------------------
# BLOCK 2: GATHERS THE ORDERS AND PREPARES THEM FOR EXPORT

# Defines orders list, current date and starting invoice number

today = date.today()
invoice_num = int(1)
orders = []

for row in fatture.iter_rows(min_row=2, max_row=fatture.max_row, min_col=1, max_col=fatture.max_column, values_only=True):
# Create order - store values in key-value pairs
    order = {"Buyer Name":row[11],
             "Shipping Address":row[25],
             "Shipping Details": f"{row[28]}, {row[29]} {row[30]}",
             "Invoice num": invoice_num, # Set initial invoice number
             "Emission Date": today.strftime("%d/%m/%Y"),
             "Order ID":row[0],
             "Items Qty": int(1),
             "Product Code":row[13],
             "Unit Price":float(row[17]),
             "Total Price":float(row[17]),
             "Date String": today.strftime("%B %d, %Y")}

    # Appends description data
    for row in descriptions.iter_rows(min_row=2, max_row=descriptions.max_row, min_col=1, max_col=descriptions.max_column, values_only=True):
        if order["Product Code"] == row[0]:
            order.update({"Description": row[3],
                          "HTS Heading": row[1],
                          "FDA Code": row[2]})
            break
    orders.append(order)
    invoice_num += 1

# Merge all rows with the same order ID
for i in reversed(range(len(orders)-1)):
    if orders[i]["Order ID"] == orders[i+1]["Order ID"]:
        orders[i]["Total Price"] += orders[i+1]["Unit Price"]
        orders[i]["Items Qty"] += 1
        orders.remove(orders[i+1])

print(orders)

#-------------------------------------------------------------------------------
# BLOCK 3: SENDS ORDER TO FORM, SAVES AS PDF AND REPEATS FOR ALL ORDERS

# Open excel in background
print("Dispatching...")
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = False
formXl = excel.Workbooks.Open(r"C:\Users\t_man\Documents\ThatsArte Fatture XL\Output Form.xlsx")

for order in orders:
    # Send order to correct cells in excel output file with default template (do not overwrite)
    cells = ["A14", "A15", "A16", "C22", "C23", "C24", "A30", "B30", "I30", "J30", "B47", "C30", "E30", "G30"]
    i = 0
    for key in order:
        form[cells[i]] = order[key]
        i += 1
    # Checkpoint
    workbook.save(r"C:\Users\t_man\Documents\ThatsArte Fatture XL\Output Form.xlsx")

    # Save excel file as pdf named with order ID
    try:
        print("Start conversion to PDF: {}".format(order["Buyer Name"]))
        formXl.ActiveSheet.ExportAsFixedFormat(0, r"C:\Users\t_man\Documents\ThatsArte Fatture XL\Fatture\Fattura {}".format(order["Order ID"]))
    except com_error as e:
        print("Failed:" + e)
    else:
        print("Success.")
    # Checkpoint
    workbook.save(r"C:\Users\t_man\Documents\ThatsArte Fatture XL\Output Form.xlsx")
    # Overwrites the excel file by looping through again

# Close workbook and PDF
formXl.Close()
excel.Quit()


The issue I am having is that when the problematic line is executed, the excel file in question is opened in the foreground of my pc. This causes the workbook.save("path") lines to fail due to [Errno 13]: Permission Denied, which basically means sorry can't save the file cause it's open. workbook.save("path") works perfectly when the file is closed and excel successfully launches in the background when excel = win32com.client.Dispatch("Excel.Application") is executed. I have the same problem when trying to more test files, so I don't believe this will be fixed by changing file properties but rather the code. I thought this ought to be solved by excel.Visible = False but apparently not.

Is there a way for me to open the workbook in the background so that I can keep iterating through orders and exporting xlsx as pdf without getting stopped by permission errors?

Everything else works fine.

What I have tried:

Different Files. Both read only and read & write.

This answer to a similar question from stack exchange

Isolating the problematic line and reproducing the problem changing one variable at the time.
Posted
Updated 17-May-20 1:39am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

I think the problem is that you are trying to save to the same location as the open file, and Windows will not let you do that. Most systems would just use Save without specifying a filename, or Close, and let the user respond when asked if the file should be saved. I have not used openpyxl but feel sure that there is a way to close the file without needing to specify its destination. Check the documentation to see what other ways to close the current file exist.
   
Comments
G_24 17-May-20 7:57am
   
Hi Richard, thanks for your answer. Openpyxl asks for a path to save the file, it throws an error otherwise. In fact, when it saves the file to the specified path, it doesn't "save as" a separate file but it saves the already existing file. This works just fine when the file in question is closed. My issue regards win32 opening the workbook in the foreground rather than in the background. Excel dispatches successfully in the background, but excel.Workbooks.Open() doesn't. After the workbook is open, openpyxl saving functions fails.
Richard MacCutchan 17-May-20 8:24am
   
Well that appears to be a problem with the openpyxl library. I have used the Microsoft interop namespace to manipulate Excel files using C#, and the Save method does not require a filename. I guess you will have to change your code so it saves it to a different filename.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100