Excel not closing using the COM in Python-CodePudding

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.

CodePudding user response：

The Workbooks property returns a Workbooks collection that represents all the open workbooks. The name or index number of the object can be used for retrieving a particular workbook instance, but the file path like shown in your code:

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

Consider using the ActiveWorkbook property which returns a Workbook object that represents the workbook in the active window (the window on top). So, the code could be rewritten in the following way:

excel.Application.ActiveWorkbook.Close

or simply:

excelApl.Close

Always deal with an object instance you retrieved from the Open method.

CodePudding user response：

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

That said, the reason for the error is that Excel only uses the workbook short name for indexing (ie 'TestExcelCOM.xlsx' with no path), which is why you can't open two workbooks with the same short name at the same time. You can sometimes get away with just 'TestExcelCom' as the index, but this will only return one workbook even if you have two files that only differ by extension (eg .xlsx and .xlsm).