Delete Named Ranges

GemBox.Spreadsheet
1

Hello there,

how to delete existing name ranges in the worksheet and create new one with the same name?
I have situation that I have two worksheets. One worksheet has defined named ranges and I need to define same named ranges on the second sheet and then delele first worksheet.
I am able to delete the named range with Delete(), but when I want to create new named range with Add with the same name as previously deleted named range, it does not work.
Do you have any examples how to work with named ranges?
I am using version 45.0.0.1206.

Thanks in advance.
Michal

2

Hi Michal,

I was unable to reproduce this issue, here is what I’ve tried:

var workbook = ExcelFile.Load("input.xlsx");
var worksheet = workbook.Worksheets["Sheet1"];
var range = worksheet.NamedRanges["MyRange1"];
range.Delete();

worksheet = workbook.Worksheets.Add("Sheet2");
worksheet.NamedRanges.Add("MyRange1", worksheet.Cells.GetSubrange("A1:B2"));

workbook.Save("output.xlsx");

Just in case, can you please try again with the current latest bugfix version:

If the problem remains, please send us a small Visual Studio project that reproduces your issue so that we can investigate it.

Regards,
Mario

3

Hello Mario, any way to make the NamedRange global?
When I try to add something like:

worksheet.NamedRanges.Add "MyRange1", worksheet.Cells.GetSubrange("A1:B2"), True

I get an error in number of parameters of Add method.

Thanks a lot
Cristian

4

Hi,

Where exactly are you using GemBox.Spreadsheet?

Is it possible that you’re accessing it through COM?
If that is the case, please try using the following instead:

workbook.DefinedNames.AddDefinedName "MyRange1", "Sheet2!A1:B2", -1

Regards,
Mario

5

Thanks @mario.gembox
Yes I’m using COM version developing in Classic ASP.
However the API Reference do not give these details/indications on what are the limitation/deviation of the class when accessing through COM Helper.

6

Hi Cristian,

Yes unfortunately, that is true.

GemBox.Spreadsheet is focused on providing API for .NET, the COM usage is secondary.

However, we do try to cover any requirement that is requested with either a new API in ComHelper or for a more complex scenario we recommend creating a wrapper DLL that will cover any task that you require from GemBox.Spreadsheet.

In any case, please try using the previous suggestion and add the named range using the DefinedNames collection.

Regards,
Mario

7

Thanks @mario.gembox
One further clarification about definednames application in ASP Classic.
There is any method to reference to a cell/range using its defined name?
I’ve tried somethng like

Set Range=MySheet.Cells.GetSubrange(“MyDefinedName”)
Range.Value=“MyValue”

without success.
Thanks a lot
Cristian

8

Hi Cristian,

After adding that global defined name, you should be able to retrieve it from the worksheet.NamedRanges collection.

Regards,
Mario

9

Hello Mario,
thanks for suggestion. I’ve solved through this code:

for N=0 to worksheet.NamedRanges.Count - 1
	if worksheet.NamedRanges.Item(N).Name=CellName then 
		worksheet.NamedRanges.Item(N).Range.Value=CellValue
		exit for
	end if
Next

Thanks again
Cristian