CODEWITHSUNDEEP
pythonexcelcomwin32com
Rafal
Rafal

Reputation: 129

win32com Excel data input error

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.

Upvotes: 1

Views: 4679

Answers (4)

Rahib
Rahib

Reputation: 512

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.

Upvotes: 0

sliders_alpha
sliders_alpha

Reputation: 2454

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

Upvotes: 0

Rafal
Rafal

Reputation: 129

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 !

Upvotes: 0

tpdorsey
tpdorsey

Reputation: 111

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.

Upvotes: 2

Related Questions