How to open excel workbook in pywin32 without impeding openpyxl saving function

See more:

Win32

Python

MS-Excel

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.