NamedRange disappears when copying a worksheet between workbooks

We encountered two problems with named ranges when copying a worksheet from a workbook to another workbook. The problem is somewhere within the Worksheet.Copy(..) function. Dependend on the (target) workbook used another problem occurs:

Problem 1:
With some worksheets TargetWorkSheet.Copy(SourceWorkSheet) throws a NullReferenceException.

Private Sub Test1()
        Console.WriteLine("Start test 1")
    <SPAN style="COLOR: blue">Dim</SPAN> objSourceWorkBook <SPAN style="COLOR: blue">As</SPAN> <SPAN style="COLOR: blue">New</SPAN> <SPAN style="COLOR: #2b91af">Workbook</SPAN>(<SPAN style="COLOR: #a31515">"Workbooks\SourceWorkbook.xls"</SPAN>)
    <SPAN style="COLOR: blue">Dim</SPAN> objTargetWorkBook <SPAN style="COLOR: blue">As</SPAN> <SPAN style="COLOR: blue">New</SPAN> <SPAN style="COLOR: #2b91af">Workbook</SPAN>(<SPAN style="COLOR: #a31515">"Workbooks\TargetWorkbook1.xls"</SPAN>)

    <SPAN style="COLOR: blue">Dim</SPAN> objSourceWorkSheet <SPAN style="COLOR: blue">As</SPAN> <SPAN style="COLOR: #2b91af">Worksheet</SPAN> = objSourceWorkBook.Worksheets(<SPAN style="COLOR: #a31515">"Numeric"</SPAN>)
    <SPAN style="COLOR: blue">Dim</SPAN> objTargetWorkSheet <SPAN style="COLOR: blue">As</SPAN> <SPAN style="COLOR: #2b91af">Worksheet</SPAN> = objTargetWorkBook.Worksheets(<SPAN style="COLOR: #a31515">"HorizontalSeries"</SPAN>)

    <SPAN style="COLOR: green">'get named range in target sheet</SPAN>
    <SPAN style="COLOR: blue">Dim</SPAN> objNamedRangeBefore <SPAN style="COLOR: blue">As</SPAN> <SPAN style="COLOR: #2b91af">Range</SPAN> = objTargetWorkBook.Worksheets.GetRangeByName(strRangeName)

    <SPAN style="COLOR: #2b91af">Console</SPAN>.WriteLine(<SPAN style="COLOR: #a31515">"Named range before copy in target: "</SPAN> & objNamedRangeBefore.RefersTo)

    <SPAN style="COLOR: blue">Try</SPAN>
        objTargetWorkSheet.Copy(objSourceWorkSheet) <SPAN style="COLOR: green">'This line throws NullReferenceException, while both worksheets exists</SPAN>
    <SPAN style="COLOR: blue">Catch</SPAN> ex <SPAN style="COLOR: blue">As</SPAN> <SPAN style="COLOR: #2b91af">NullReferenceException</SPAN>
        <SPAN style="COLOR: #2b91af">Console</SPAN>.WriteLine(ex.ToString())
    <SPAN style="COLOR: blue">End</SPAN> <SPAN style="COLOR: blue">Try</SPAN>

    <SPAN style="COLOR: blue">Dim</SPAN> objNamedRangeAfter <SPAN style="COLOR: blue">As</SPAN> <SPAN style="COLOR: #2b91af">Range</SPAN> = objTargetWorkBook.Worksheets.GetRangeByName(strRangeName)

    <SPAN style="COLOR: #2b91af">Console</SPAN>.WriteLine(<SPAN style="COLOR: #a31515">"Named range after copy  in target: "</SPAN> & objNamedRangeAfter.RefersTo)
    <SPAN style="COLOR: #2b91af">Console</SPAN>.WriteLine()
    <SPAN style="COLOR: #2b91af">Console</SPAN>.WriteLine()
    <SPAN style="COLOR: #2b91af">Console</SPAN>.WriteLine()
<SPAN style="COLOR: blue">End</SPAN> <SPAN style="COLOR: blue">Sub</SPAN></PRE>

Problem 2:
One of the named ranges in the target workbook no longer exists after the copy.

    Private Sub Test2()
        Console.WriteLine("Start test 2")
        Dim objSourceWorkBook As New Workbook("Workbooks\SourceWorkbook.xls")
        Dim objTargetWorkBook As New Workbook("Workbooks\TargetWorkbook2.xls")
    <SPAN style="COLOR: blue">Dim</SPAN> objSourceWorkSheet <SPAN style="COLOR: blue">As</SPAN> <SPAN style="COLOR: #2b91af">Worksheet</SPAN> = objSourceWorkBook.Worksheets(<SPAN style="COLOR: #a31515">"Numeric"</SPAN>)
    <SPAN style="COLOR: blue">Dim</SPAN> objTargetWorkSheet <SPAN style="COLOR: blue">As</SPAN> <SPAN style="COLOR: #2b91af">Worksheet</SPAN> = objTargetWorkBook.Worksheets(<SPAN style="COLOR: #a31515">"HorizontalSeries"</SPAN>)

    <SPAN style="COLOR: green">'get named range in target sheet</SPAN>
    <SPAN style="COLOR: blue">Dim</SPAN> objNamedRangeBefore <SPAN style="COLOR: blue">As</SPAN> <SPAN style="COLOR: #2b91af">Range</SPAN> = objTargetWorkBook.Worksheets.GetRangeByName(strRangeName)

    <SPAN style="COLOR: #2b91af">Console</SPAN>.WriteLine(<SPAN style="COLOR: #a31515">"Named range before copy in target: "</SPAN> & objNamedRangeBefore.RefersTo)

    objTargetWorkSheet.Copy(objSourceWorkSheet) <SPAN style="COLOR: green">'The copy seems to work ok with this target worksheet</SPAN>

‘But when trying to use the named range that existed before the copy, that range is now missing

Dim objNamedRangeAfter As Range = objTargetWorkBook.Worksheets.GetRangeByName(strRangeName)
If objNamedRangeAfter Is Nothing Then
Console.WriteLine(“Named range with name '” & strRangeName & "’ disappeared!")
Else
Console.WriteLine("Named range after copy in target: " & objNamedRangeAfter.RefersTo)
End If

    objTargetWorkBook.Save(<SPAN style="COLOR: #a31515">"Test2 - WorkbookAfterSheetCopy.xls"</SPAN>)
    <SPAN style="COLOR: #2b91af">Console</SPAN>.WriteLine()
    <SPAN style="COLOR: #2b91af">Console</SPAN>.WriteLine()
    <SPAN style="COLOR: #2b91af">Console</SPAN>.WriteLine()

<SPAN style="COLOR: blue">End</SPAN> <SPAN style="COLOR: blue">Sub</SPAN>

 
I attached a test project that reproduces both problems (with Aspose.Cells 7.4.0.4) 
The only difference between the 2 test situations is a difference in the TargetWorkbook. 
To create TargetWorkbook2.xls I removed the first worksheet from TargetWorkbook1.xls. (The removed worksheet did not contain the disappearing range)
We did find a work around for this problem:
When a named range (on a worksheet other than the one being copied and with the same name as the disappearing range) is added to the source workbook the problem seems to be solved.

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We were able to notice these issues. One test case throws nullreference exception and other test case says, namedrange disappeared.We have logged this issue in our database. We will look into it and resolve this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41839.

Hi,

Thank you for registering this issue.

With kind regards,

Hans
Software Engineer
Infoland BV

(Registered bugs in our system caused by this issue: #39637, #39873, #39894, #40167)

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have fixed the issue.

Please download and try this fix: Aspose.Cells for .NET v7.5.1.1 and let us know your feedback.

Hi,

After initial testing, the problem seems to be fixed.

With kind regards,

Hans
Software Engineer
Infoland BV

Hi,


Good to know that your issue is resolved by the new fix. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan