Spreadsheet failed to open after updating a worksheet table in Excel using openpyxl Python package

Question

I have a spreadsheet that contains several defined tables. My goal is to use Python to update the data in one of those tables. Each time, the number of records (rows) is subject to change, so based on this thread, I need to adjust tableRef and tableStyleInfo if table size and style are changed. After those changes have been made and the Excel has been saved, I was not able to open the spreadsheet anymore.

The error message the COM Interface gives is com_error: (-2147023170, 'The remote procedure call failed.', None, None). When I open the spreadsheet manually, the Excel window just disappeared after it finishes loading.

Below is the code I used update the spreadsheet. A1:D2500 is the original table range including the header. My code simply starts with clearing all existing data in the table.

for row in mysheet_LT['A2:D2500']:
    for cell in row:
        cell.value = None

r=1

for per in ['AM' ,'PM']:
    for link in all_veh_attributes:
        r+=1
        ID=int(link[0])
        length=float(link[1])
        if link[2]==None:
            demand=0
        else:
            demand=int(link[2])
        
        mysheet_LT.cell(r,1).value=ID
        mysheet_LT.cell(r,2).value=per
        mysheet_LT.cell(r,3).value=length
        mysheet_LT.cell(r,4).value=demand
    print(per+" finished.")



style=TableStyleInfo(name="TableStyleMedium2", showFirstColumn=False,
                   showLastColumn=False, showRowStripes=True, showColumnStripes=False)
for i, table in enumerate(mysheet_LT._tables):
    if table.name=="LinkTable":
        tableRef=i
        break

resTable = Table(displayName="LinkTable", ref="A1:{}{}".format("D",r))
resTable.tableStyleInfo = style

mysheet_LT._tables[tableRef] = resTable
f_excel.save(filepath.replace(".xlsx","_copy.xlsx"))
del f_excel

I also checked the source xml files saved in xl\tables to figure out what was changed behind the scene other that the data values. Before I ran the script and spreadsheet is still loadable, the table1.xml is shown below.

<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<table totalsRowShown="0" ref="A1:D2500" displayName="LinkTable" name="LinkTable" xr:uid="{ED5889A4-4026-48FE-85A0-F3DC88F0CD25}" id="6" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" mc:Ignorable="xr xr3" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<autoFilter ref="A1:D2500" xr:uid="{ED5889A4-4026-48FE-85A0-F3DC88F0CD25}"/>
-<tableColumns count="4">
<tableColumn name="Link" xr3:uid="{D25FAC3E-75B1-47FF-B538-B5AAC2E676D2}" id="1"/>
<tableColumn name="Period" xr3:uid="{FB8F2531-3D44-4F33-8013-7EBE0D747A16}" id="2"/>
<tableColumn name="Length" xr3:uid="{F726E2D7-F77C-46B7-A5E5-1A7BFF89FD6F}" id="3"/>
<tableColumn name="Demand" xr3:uid="{5A968ACB-7F4B-4E5D-9480-E4DDCAD39B79}" id="4"/>
</tableColumns>
<tableStyleInfo name="TableStyleMedium2" showColumnStripes="0" showRowStripes="1" showLastColumn="0" showFirstColumn="0"/>
</table>

Below is the same file in the same folder after the Python script was run and the spreadsheet failed to open. I noticed that all the xr3:uids are missing here, but I am not sure if that's what caused the issue. If that's the problem, could someone tell me how I can properly defined them in openpyxl.

-

<autoFilter ref="A1:D1835"/>
-<tableColumns count="4">
<tableColumn name="Link ID" id="1"/>
<tableColumn name="Period" id="2"/>
<tableColumn name="Length" id="3"/>
<tableColumn name="Demand" id="4"/>
</tableColumns>
<tableStyleInfo name="TableStyleMedium2" showColumnStripes="0" showRowStripes="1" showLastColumn="0" showFirstColumn="0"/>
</table>