We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Error on addressing Range by name

Thread ID:

Created:

Updated:

Platform:

Replies:

64014 Jul 12,2007 12:46 PM Jul 17,2007 10:10 AM Windows Forms 4
loading
Tags: XlsIO
Christian Nein
Asked On July 12, 2007 12:46 PM

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 [Syncfusion]
Replied On July 13, 2007 02:44 AM

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
Replied On July 16, 2007 09:32 AM

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 [Syncfusion]
Replied On July 17, 2007 10:09 AM

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 [Syncfusion]
Replied On July 17, 2007 10:10 AM

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

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;