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 IfobjTargetWorkBook.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.