Do we have to open excel file EVERYTIME we write in it with Python?

Question

I have to write on an Excel file using Python. I use win32com to do it.

I want to open it first do some stuff then write in my file do more stuff and write again in the file.

but I if I don't open the file everytime before writing, i have this error message:

pywintypes.com_error: (-2146827864, 'OLE error 0x800a01a8', None, None)

here my code :

connection = pypyodbc.connect('Driver={SQL Server};'
                'Server=SRVAKTCT-SQL\TCTSQL;'
                'Database=K;'
                'uid=Y;pwd=x')

cursor = connection.cursor()

for log, TA in zip(listeID,ListeTA):
    NomTA=TA[0]
    Equipe=TA[1]
    if log:

        #doing stuff
        results = something
        temps_log=results[0]
        print(temps_log)
        if temps_log is not None:
            temps_log=str(datetime.timedelta(seconds=int(temps_log)))

        excel = win32.gencache.EnsureDispatch('Excel.Application')
        wb = excel.Workbooks.Open('//Srvaktct-bur02/Copie de vide.xlsx')
        ws=wb.Worksheets(date_onglet)
        ws.Cells(ligne_cumul,10).Value=temps_log
        #wb.Close(True)
    #wb = excel.Workbooks.Open('//Srvaktct-bur02/Copie de vide.xlsx')
    #ws=wb.Worksheets(date_onglet)
    ws.Cells(ligne_cumul,2).Value=NomTA
    ws.Cells(ligne_cumul,3).Value=Equipe

    wb.Close(True)
    excel.Application.Quit()
    ligne_cumul += 1

Here the code works only if I uncomment the comment region.

Answer 1

if I don't open the file everytime before writing, i have this error message:

Well yeah, because if you don't have a file object to write to, what else would you expect to happen?

You're doing a wb.Close() call within the loop, so, because you close it, you have to also re-open it if you want to write to it (or read from it) again. You're closing/opening the file twice witin the loop, and you're also doing the excel.Quit operation inside the loop, which requires that you re-instantiate it at each interation Better approach is to instantiate excel outside of the loop (and later Quit it after loop terminates).

Untested,but see if it will help (revised because you mention it is the same file)

connection = pypyodbc.connect('Driver={SQL Server};'
                'Server=SRVAKTCT-SQL\TCTSQL;'
                'Database=K;'
                'uid=Y;pwd=x')

cursor = connection.cursor()
wb, ws = None, None
filePath = '//Srvaktct-bur02/Copie de vide.xlsx'
# Get a handle on Excel application:
excel = win32.gencache.EnsureDispatch('Excel.Application')
# Open the file outside of the loop, too:
wb = excel.Workbooks.Open(filePath)
ws=wb.Worksheets(date_onglet)
for log, TA in zip(listeID,ListeTA):
    NomTA=TA[0]
    Equipe=TA[1]
    if log:
        #doing stuff
        results = something
        temps_log=results[0]
        print(temps_log)
        if temps_log is not None:
            temps_log=str(datetime.timedelta(seconds=int(temps_log)))
            ws.Cells(ligne_cumul,10).Value=temps_log
    ws.Cells(ligne_cumul,2).Value=NomTA
    ws.Cells(ligne_cumul,3).Value=Equipe
    ligne_cumul += 1
# Don't close wb or Quit Excel inside the loop!
wb.Close(True)
excel.Application.Quit()