Delete all Excel named ranges (local and global scope)

[pfdjhfuys]

Hi,

I have an Excel Workbook. I am using the openpyxl package. I don't know how many worksheets I have nor do I know the named ranges.

My goal is to remove all named ranges.

wb.defined_names.get gives me a class 'openpyxl.workbook.defined_name.DefinedNameList' but I can't find the scope. Below is one output:

.
.
.
Parameters:
name='ref_fx_qs', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=8, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='#REF!', <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='ref_fx_qs', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=9, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='#REF!', <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='ref_fx_qs', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='#REF!', <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='ref_std_itd_on', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=4, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text="'2018 CY'!$AL$22", <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='ref_std_itd_on', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=3, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text="'2019 CY'!$AL$22", <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='ref_std_itd_on', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=2, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text="'2020 CY'!$AL$22", <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='ref_std_itd_on', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=7, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='#REF!', <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='ref_std_itd_on', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=5, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='#REF!', <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='ref_std_itd_on', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=6, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='#REF!', <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='ref_std_itd_on', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=10, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='#REF!', <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='ref_std_itd_on', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=8, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='#REF!', <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='ref_std_itd_on', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=9, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='#REF!', <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='ref_std_itd_on', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='#REF!', <openpyxl.workbook.defined_name.DefinedName object>
.
.
.

Thanks

[Larz60+]

Documentation is here
You can get a list of sheet names with wb.sheetnames (assumes workbook named wb, modify for your name)

[pfdjhfuys]

Documentation is here
You can get a list of sheet names with wb.sheetnames (assumes workbook named wb, modify for your name)

I had managed to get the sheets; I wanted to delete all named ranges (both local and global in scope) .

The following did it:

    wb = load_workbook(filename = file, data_only=True)

    # Get the global named ranges
    named_ranges = wb.defined_names

    # Print the names
    index = 0
    while len(named_ranges.definedName) > index:
        name = named_ranges.definedName[index].name  
        sheetID = named_ranges.definedName[index].localSheetId
        if sheetID == None:    
            del wb.defined_names[name]  # you can also call wb.defined_names.delete(name)
        else:
            wb.defined_names.delete(name, sheetID)

    wb.save(file)
    wb.close()