Python Forum

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

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

(Mar-23-2023, 11:45 AM)Larz60+ Wrote: [ -> ]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()