Excel not closing using the COM in Python

0 votes

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.

Jan 24, 2023 in Others by Kithuzzz
• 38,000 points
2,752 views

1 answer to this question.

0 votes

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).

answered Jan 24, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Excel VLOOKUP where the key is not in the first column

INDEX/MATCH will do it in any direction of ...READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 63,600 points
1,491 views
0 votes
1 answer

Calculating the mean and std on excel file using python

Use pandas to do this: import pandas as pd df = ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,600 points
2,245 views
0 votes
1 answer

Using a UDF in Excel to update the worksheet

The MSDN KB is incorrect. It says A user-defined function called ...READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 63,600 points
729 views
0 votes
1 answer

Update an Excel sheet in real time using Python

Using xlwings, I have really discovered how ...READ MORE

answered Nov 13, 2022 in Others by narikkadan
• 63,600 points
3,185 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,449 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Convert table in a jpg image to excel using python

I believe you must execute OCR (optical ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,600 points
2,285 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP