win32com Excel data input error

Question

I'm exporting results of my script into Excel spreadsheet. Everything works fine, I put big sets of data into SpreadSheet, but sometimes an error occurs:

             File "C:\Python26\lib\site-packages\win32com\client\dynamic.py", line 550, in __setattr__
                self._oleobj_.Invoke(entry.dispid, 0, invoke_type, 0, value)
            pywintypes.com_error: (-2147352567, 'Exception.', (0, None, None, None, 0, -2146777998), None)***

I suppose It's not a problem of input data format. I put several different types of data strings, ints, floats, lists and it works fine. When I run the sript for the second time it works fine - no error. What's going on?

PS. This is code that generates error, what's strange is that the error doesn't occur always. Say 30% of runs results in an error. :

import win32com.client
def Generate_Excel_Report():    
    Excel=win32com.client.Dispatch("Excel.Application")
    Excel.Workbooks.Add(1)    
    Cells=Excel.ActiveWorkBook.ActiveSheet.Cells
    for i in range(100):
        Row=int(35+i)
        for j in range(10):                      
            Cells(int(Row),int(5+j)).Value="string"
    for i in range(100):
        Row=int(135+i)
        for j in range(10):

            Cells(int(Row),int(5+j)).Value=32.32 #float

Generate_Excel_Report()

The strangest for me is that when I run the script with the same code, the same input many times, then sometimes an error occurs, sometimes not.

If possible, a piece of code that people can run and recreate the error would be very helpful. If not, at least post the offending piece of your code which causes this error to occur, and an actual input example (with values and without comments mixed inside). — taleinat, Jun 05 '10 at 07:52
It does not address your question, but I've had good luck with the `xlrd` and `xlwt` modules: http://www.python-excel.org. — FMc, Jun 05 '10 at 10:14
Nothing to do with your problem, but why do you do `int(foo)` where `foo` is already an `int`??? — John Machin, Jun 06 '10 at 12:54
John Machin: I was just trying find out where the error can be, but it was not there. Int(Int)=Int so it doesn't change anything actually. — Rafal, Jun 06 '10 at 13:02
Have you tried setting `Excel.Visible = True`? It's possible that some GUI code (waiting for screen update?) is causing Excel to throw up. — Ryan Ginstrom, Jun 07 '10 at 13:08
`Excel.Visible=True` doesn't change a thing. Actually after putting few hundreds of strings, integers and floats Excel says stop: ` self._oleobj_.Invoke(entry.dispid, 0, invoke_type, 0, value) pywintypes.com_error: (-2147352567, 'Wyst\xb9pi\xb3 wyj\xb9tek.', (0, None, None, None, 0, -2146777998), None) ` — Rafal, Jun 07 '10 at 16:08

score 2 · Answer 1 · edited May 23 '17 at 12:06

This is most likely a synchronous COM access error. See my answer to Error while working with excel using python for details about why and a workaround.

I can't see why the file format/extension would make a difference. You'd be calling the same COM object either way. My experience with this error is that it's more or less random, but you can increase the chances of it happening by interacting with Excel while your script is running.

score 0 · Answer 2 · edited Oct 10 '13 at 07:41

0

edit: It doesn't change a thing. Error occurs, but leff often. Once in 10 simulations while with .xlsx file once in 3 simulations. Please help

The problem was with the file I was opening. It was .xlsx , while I've saved it as .xls the problem disappeared. So beware, do not ever use COM interface with .xlsx or You'll get in trouble !

edited Oct 10 '13 at 07:41

iLovePython

261
4
14

answered Jun 07 '10 at 09:15

Rafal

129
1
3
7

2

I use COM interface with .xlsx at work everyday, we have 15 files generated each day wich contains thousand of records. I don't get errors. – sliders_alpha Oct 12 '15 at 13:32

score 0 · Answer 3 · answered Oct 12 '15 at 13:41

You should diseable excel interactivity while doing this.

import win32com.client
def Generate_Excel_Report():    
    Excel=win32com.client.Dispatch("Excel.Application")
    #you won't see what happens (faster)
    Excel.ScreenUpdating = False
    #clics on the Excel window have no effect
    #(set back to True before closing Excel)
    Excel.Interactive = False

    Excel.Workbooks.Add(1)    
    Cells=Excel.ActiveWorkBook.ActiveSheet.Cells
    for i in range(100):
        Row=int(35+i)
        for j in range(10):                      
            Cells(int(Row),int(5+j)).Value="string"
    for i in range(100):
        Row=int(135+i)
        for j in range(10):

            Cells(int(Row),int(5+j)).Value=32.32 #float

    Excel.ScreenUpdating = True
    Excel.Interactive = True

Generate_Excel_Report()

Also you could do that to increase your code performance :

#Construct data block

string_line = []
for i in range(10)
string_line.append("string")
string_block = []
for i in range(100)
string_block.append(string_line)


#Write data block in one call
ws = Excel.Workbooks.Sheets(1)
ws.Range(
    ws.Cells(35, 5)
    ws.Cells(135,15)
    ).Values = string block

score 0 · Answer 4 · answered Nov 02 '20 at 05:21

I had the same error while using xlwings for interacting with Excel. xlwings also use win32com clients in the backend. After some debugging, I realized that this error pops up whenever the code is executed and the excel file (containing data) is not in focus. In order to resolve the issue, I simply select the file which is being processed and run the code and it always works for me.

win32com Excel data input error

4 Answers4