Name Conflict dialog while using win32com in Python

[Leopold_1]

Hello,

im having some problems with the name conflict appearing in Excel while using the win32com API for Python. 

What im trying to do is to create an Excel file and filling it with data. The data gets filtered and saved. Afterwards the file gets opened again and a diffrent filtering is applied and the file again saved. Once this is done if im trying to open it again thru python the naming conflict occures as seen in the screenshot. If i just double click the file no error message is shown. Please see the attached source code. This can be used to recreate the problem. Any help or idea would be appreciated.

Thanks in advance,

Leopold

import win32com.client as win32

def main():
        path = "C:\\Users\\*YourUsername*\\Desktop\\NameConflict1.xlsx"    #Please adjust your username
        excel = win32.gencache.EnsureDispatch('Excel.Application')
        excel.Visible = True
        wb = excel.Workbooks.Add()
        wb.Worksheets(1).Name = "Test1"
        ws = wb.Worksheets("Test1")
        ws.Cells(1,1).Value = "Day"    
        ws.Cells(1,2).Value = "Weather"    
        ws.Cells(2,1).Value = "Monday"    
        ws.Cells(2,2).Value = "Sunny"
        ws.Cells(3,1).Value = "Tuesday"    
        ws.Cells(3,2).Value = "Raining"
        ws.Columns.AutoFilter(1)
        ws.Range("A1:C2").AutoFilter(Field=1, Criteria1="Monday", Operator=7)
        wb.Close(True, path )
        excel.Application.Quit()
        excel.Quit()

        excel2 = win32.gencache.EnsureDispatch('Excel.Application')
        excel2.Visible = True
        wb2 = excel2.Workbooks.Open(path)
        ws2 = wb2.Worksheets("Test1")
        ws2.Range("A1:C2").AutoFilter(Field=1, Criteria1="Tuesday", Operator=7)
        wb2.Close(True, path )
        excel2.Application.Quit()
        excel2.Quit()

        excel3 = win32.gencache.EnsureDispatch('Excel.Application')
        excel3.Visible = True
        wb3 = excel3.Workbooks.Open(path)   #Name conflict occures here
        print("Hello")
 
if __name__ == "__main__":
    main()

[#2 · Accepted solution]

@Leopold_1 

My knowledge of Python is, to put it mildly, very modest, but with Excel better :)...

Based on the code you provided, it is not clear why the name conflict issue is occurring.

One possible cause could be that the AutoFilter method is creating a defined name in the workbook that conflicts with an existing name.

You could try adding some code to check for and remove any duplicate or conflicting defined names in the workbook before saving it.

Here is an example of how you could do this:

for name in wb.Names:

    if name.Name == "ConflictingName":

        name.Delete()

This code will loop through all the defined names in the workbook and delete any that have the name “ConflictingName”.

You can modify this code to check for and delete any duplicate or conflicting names that might be causing the issue.

[#3]

Thanks @NikolinoDE !

This helped a lot and solved the issue. Even though the names are not visible using the Excel names manager in the application with your approach i saw that everytime an autofilter is used a name (in this example {'Test1!_FilterDatabase'}" gets added to the workbook.  

I slightly adjusted the code to delete duplicate entries after each usage of the autofilter.

seen = set()
for name in wb2.Names:
                if name.Name in seen:
                        name.Delete()
                else:
                        seen.add(name.Name) 

[#4]

I am glad that I could help you in your project.
I wish you continued success with Excel!