python3 and pywin32 closing excel

Question

I am having an issue closing excel after using Dispatch.

import openpyxl
import os 
from win32com import client



class CTAutomation:

    def __init__(self, file):
        self.invoice = xl.load_workbook(os.getcwd() + "\Templates\ctrates.xlsx")
        self.xlTemplate = xl.load_workbook(os.getcwd() + "\Templates\invoiceTemplate.xlsx")
        self.vpc = xl.load_workbook(os.getcwd() + "\Templates\Vpc.xlsx")
        self.file = file

    def invoice_make(self):
        self.xlApp = client.Dispatch("Excel.Application")
        self.xlbook = self.xlApp.Workbooks.Open(os.getcwd() + '\TestFiles\\' + self.file)
        self.ws = self.xlbook.Worksheets[0]
        self.ws.Visible = 1
        self.ws.ExportAsFixedFormat(0, os.getcwd() + "\complitedpdf\\" + self.file + ".pdf")
        self.quit()

    def quit(self):
        self.xlbook.Close()
        self.xlApp.Quit()

    def xlformater(self):
        return None

def main():
    pwd = os.listdir(os.getcwd() + "\TestFiles")
    for file in pwd:
        CTAutomation(file.strip(".xlsx")).invoice_make()

if __name__ == "__main__":
    main()

all works well till this part. i have found a few posts about this topic in the forum but i feel that im still missing something to close the app, .xlsx and xls(Latest Versions) to pdf using python in example

some advice would be much appreciated .

I see no trial or attempt here to close any object. Plus you give a snippet of a larger class. Hard to help without adequate, compilable [reproducible example](https://stackoverflow.com/help/mcve). — Parfait, Jul 27 '17 at 03:05
Added full class - running this will result in an infinite loop — Mike.G, Jul 27 '17 at 03:10
Please include all `import` lines. It is unclear where `load_workbook()` method derives or what alias `xl` references. Posted code must standalone as a fully compilable script. Don't assume anything. Put yourself in our shoes and makes sure examples can run on their own. — Parfait, Jul 27 '17 at 13:55
Do you get an error or your excel app just doesn't close? Also did you check excel to see if it is showing a popup to save the sheet? — Tarun Lalwani, Jul 27 '17 at 18:39
My excel app just wont close, if i run the app again because the app didn't close then i get a pop up asking if to reopen the file — Mike.G, Jul 27 '17 at 20:30
im not trying to save the sheet i just want to convert it to PDF — Mike.G, Jul 27 '17 at 20:38

Parfait · Accepted Answer · 2017-07-28T12:43:27.170

Essentially it is your class object persisting in memory. Consider wrapping the process in a context manager using with(). And call the invoice_make() within the context.

Additionally, you had an incorrect Excel method by indexing workbook by zero with square brackets.

Finally, consider using os.path.join() to aviod back or forward slashes and use a try/except block to catch COM exceptions and properly release objects from memory.

import openpyxl as xl
import os 
from win32com import client

cwd = os.getcwd()

class CTAutomation:

    def __init__(self):
        self.invoice = xl.load_workbook(os.path.join(cwd, "Templates", "ctrates.xlsx"))
        self.xlTemplate = xl.load_workbook(os.path.join(cwd, "Templates", "invoiceTemplate.xlsx"))
        self.vpc = xl.load_workbook(os.path.join(cwd, "Templates", "Vpc.xlsx"))

    def invoice_make(self, file):
        try:
            self.xlApp = client.Dispatch("Excel.Application")
            self.xlbook = self.xlApp.Workbooks.Open(os.path.join(cwd, "TestFiles", file))
            self.ws = self.xlbook.Worksheets(1)       # USE PARENTHESES (NOT BRACKETS AND NON-ZERO INDEX)
            #self.ws.Visible = 1                      # KEEP PROCESS IN BACKGROUND
            self.ws.ExportAsFixedFormat(0, os.path.join(cwd, "complitedpdf", file.replace(".xlsx",".pdf")))
            self.xlbook.Close(False)
            self.xlApp.Quit()

        except Exception as e:
            print(e)

        finally:
            self.ws = None                            # RELEASE EXCEL OBJS FROM MEMORY
            self.xlbook = None
            self.xlApp = None

    def xlformater(self):
        return None

    def __enter__(self):
        return self                                   # BOUND TO as IN with()

    def __exit__(self, *err):
        return None

def main():
    pwd = os.listdir(os.path.join(cwd, "TestFiles"))   

    with CTAutomation() as obj:                       # CONTEXT MANAGER
        for file in pwd:
            print(file)
            obj.invoice_make(file)

if __name__ == "__main__":
    main()

Thank you for the help, i made slight changes as the file was saved in the "TestFiles" folder and i assume it happened because the file was passed along with its full path, so i cut the full path and made an explicit path for "xlbook" so the export will have only the file name wihtout its full path. — Mike.G, Jul 28 '17 at 04:49
Great! Glad to help. I learned something too here. Ah! You're right about paths. Yes pass filename only into method and concatenate paths with `os.path.join()` inside. See edit. — Parfait, Jul 28 '17 at 12:40

python3 and pywin32 closing excel

1 Answers1