Error on addressing Range by name

[Christian Nein]

Hi,

I have a workbook containing multiple sheets. Each of the sheets has a range named "Title". When I try to address these ranges by name, I always get the range of the first sheet:

...
IWorksheet worksheet;
IRange range;

worksheet = workbook.Worksheets[1];
range = worksheet.Range["Title"];
range.Text = "Sheet 1";

worksheet = workbook.Worksheets[2];
range = worksheet.Range["Title"];
range.Text = "Sheet 2"; // Text is set to range "Title" on first sheet

worksheet = workbook.Worksheets[3];
range = worksheet.Range["Title"]; // Text is set to range "Title" on first sheet
range.Text = "Sheet 3";

If I do address the ranges by row and col, everything works fine:

worksheet = workbook.Worksheets[1];
range = worksheet.Range[1, 1];
range.Text = "Sheet 1";

worksheet = workbook.Worksheets[2];
range = worksheet.Range[1, 1];
range.Text = "Sheet 2"; // Text is set to the correct range

worksheet = workbook.Worksheets[3];
range = worksheet.Range[1, 1];
range.Text = "Sheet 3"; // Text is set to the correct range

Thanks & best regards
Christian

[Melba Winshia]

Hi Christian,

Thank you for posting your query to us.

I am afraid that I was not able to reproduce the issue. Please refer to the following steps that I tried to reproduce it.

1. Created a workbook containing multiple sheets in which each of the sheets has a range named "Title".
2. accessed the name by ranges using XlsIO and the ranges of each sheet are correctly accessed.

Also I have created a simple sample to test this issue and it is available in the following link.

http://websamples.syncfusion.com/samples/XlsIO.Windows/F64014/main.htm

Please have a look at the above sample and if still the issue exists, could you please try reproducing it in the above sample or send us the reproducing steps so that we could sort out the cause of the issue and provide you a solution?

Regards,
Melba

[Christian Nein]

Hi Melba,

one special thing I see is that in the beginning, the workbook contains the range only once because the sheet exists only once. But during runtime, I am generating the other sheets by creating copies from the existing sheet. Here is the function that creates the copy:

public IWorksheet ExportXlsIOCopyTemplateSheet(IWorkbook workbook, string templateSheet) //, string insertAfterName) // Excel._Worksheet insertAfter)
{
IWorksheet wsTemplate = null;
IWorksheet wsNew = null;

try
{
wsTemplate = workbook.Worksheets[templateSheet];
if (wsTemplate != null)
{
wsNew = workbook.Worksheets.AddCopyBefore(wsTemplate, wsTemplate);

}
wsNew.Select(); // Missing.Value);
}
catch (Exception ex)
{
Tracing.TraceException(ex);
}

return wsNew;
}

... and here is an exemple function call:
winbase.ExportXlsIOCopyTemplateSheet(_XlsIOWorkbook, "General"); // _XlsIOWorkbook contains the template sheet, the copy should be inserted right before the template sheet

This function I use to address the ranges:
protected IRange ExportXlsIOGetRange(IWorksheet worksheet, string reference)
{
IRange range = null;

try
{
range = worksheet.Range[reference];
}
catch { }

return range;
}

.. and this is an example function call:
IRange range = ExportXlsIOGetRange(worksheet, "_Title_"); // worksheet alsways is a newly generated copy of the template sheet

Attached I send you the XLT-file which we are using as template.

Thanks for your help and best regards
Christian

DeltaMaster.zip

[Melba Winshia]

Hi christian,

I am able to see the issue that you have mentioned here. But usually when you name a range, the name

is at the workbook level, meaning that one name refers to a specified range on a specified worksheet

wherever it is used in the workbook. Once the name has been used, it cannot be used again to

represent a range on another worksheet. So When you try to address these ranges by name always you

will get range of the first sheet while creating copies from the existing sheet.

Please refer the following link for more details.

http://www.brainbell.com/tutorials/ms-office/excel/Use_The_Same_Name_For_Ranges_On_Different_Workshee

ts.htm

Please let me know if you have any other questions.

Thanks,
Melba

[Melba Winshia]

Hi Christian,

I am able to see the issue that you have mentioned here. But usually when you name a range, the name is at the workbook level, meaning that one name refers to a specified range on a specified worksheet wherever it is used in the workbook. Once the name has been used, it cannot be used again to represent a range on another worksheet. So When you try to address these ranges by name always you will get range of the first sheet while creating copies from the existing sheet.

Please refer the following link for more details.

http://www.brainbell.com/tutorials/ms-office/excel/Use_The_Same_Name_For_Ranges_On_Different_Worksheets.htm

Please let me know if you have any other questions.

Thanks,
Melba