Excel not closing using the COM in Python

Question

My Excel workbook not closed by executing the highlighted last statement.But throws the error as below..

My code....

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')

excel.Visible= True

excelApl = excel.Application.Workbooks.Open ("C:\Technical\AutomationScripts\TestExcelCOM.xlsx")
a = excelApl.Worksheets("Sheet1").Range("A2").Value = "Stay with me"
a = excelApl.Worksheets("Sheet1").Range("A2").Value
print(a)
a = excelApl.Worksheets("Sheet1").Range("A2").Value = "Hello"
print(a)

**excel.Application.Workbooks("C:\Technical\AutomationScripts\TestExcelCOM.xlsx").Close**

the last statement throws the below error....

pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)
>>> Stay with me
Hello
Traceback (most recent call last):
  File "C:\Program Files\Python39\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py", line 363, in RunScript
    exec(codeObject, __main__.__dict__)
  File "C:\Technical\AutomationScripts\Testscript.py", line 13, in <module>
    excel.Application.Workbooks("C:\Technical\AutomationScripts\TestExcelCOM.xlsx").Close
  File "C:\Users\jpillai1\AppData\Local\Temp\gen_py\3.9\00020813-0000-0000-C000-000000000046x0x1x9\Workbooks.py", line 198, in __call__
    ret = self._oleobj_.InvokeTypes(0, LCID, 2, (13, 0), ((12, 1),),Index
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)
>>> 

Wanted to close the workbook.

Answer 1

There's a lot of superfluous assignment and indirection going on: better to create some temporary variables as you go along.

Rewritten:

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')

excel.Visible= True

wb = excel.Workbooks.Open('C:\Technical\AutomationScripts\TestExcelCOM.xlsx')
ws = wb.Worksheets('Sheet1')
rng = ws.Range('A2')

rng.Value = 'Stay with me'
print(rng.Value)

rng.Value = 'Hello'
a = rng.Value
print(a)

wb.Close(False) #Don't save changes

As a result, you cannot open two workbooks with the same short name simultaneously because Excel only utilises the workbook short name for indexing (i.e., "TestExcelCOM.xlsx" without a path). Even if you have two files that simply differ by extension, you can occasionally get away with using just "TestExcelCom" as the index, but this will only yield one workbook (eg .xlsx and .xlsm).