Hi,
We have code where we copy a sheet into a new workbook and then we copy that copied sheet within that new workbook. This works fine. However I did notice that similar excel code will result in two named ranges of scope limited to each sheet respectively whereas aspose creates the DATA range for the initial copy as scope workbook and then the second copy within workbook as scope of sheet.
The main issue is that when we execute the same code, but this time into the existing workbook from the first time (deleting each sheet before copying again), the named ranges of the original sheet do not work. There is only one as opposed to two and it has #REF issues.
I have attached a file containing the initial workbook with a range called DATA. There is also a copy of what it looks like first time through where as expected there are two named ranges called DATA (although as previously mentioned one has scope of workbook which is different to how excel treats similar operations). Then what it looks like after a second run. You can see in ToWB.xlsx there is only one named range of data (as opposed to two) and it has #REF issues.
Here is some code that will replicate the issue:
Dim loFromStr As System.IO.Stream
Dim loFromWB As Aspose.Cells.Workbook
loFromStr = New System.IO.FileStream("C:\Temp\FromWB.xlsx", IO.FileMode.Open, IO.FileAccess.ReadWrite, IO.FileShare.Read)
loFromWB = New Aspose.Cells.Workbook(loFromStr)
loFromStr.Close()
Dim loToWB As New Aspose.Cells.Workbook(Aspose.Cells.FileFormatType.Xlsx)
loToWB.Worksheets(0).Copy(loFromWB.Worksheets(0))
loToWB.Worksheets(0).Name = "Hide"
loToWB.Worksheets.Add(Aspose.Cells.SheetType.Worksheet)
loToWB.Worksheets(1).Copy(loToWB.Worksheets("Hide"))
loToWB.Worksheets(1).Name = "Main"
loToWB.Worksheets("Hide").SetVisible(False, True)
loToWB.Save(fileName:="C:\Temp\ToWB.xlsx", saveFormat:=Aspose.Cells.FileFormatType.Xlsx)
loToWB = Nothing
Dim loToStr As System.IO.Stream
loToStr = New System.IO.FileStream("C:\Temp\ToWB.xlsx", IO.FileMode.Open, IO.FileAccess.ReadWrite, IO.FileShare.Read)
loToWB = New Aspose.Cells.Workbook(loToStr)
loToStr.Close()
loToWB.Worksheets.RemoveAt("Hide")
loToWB.Worksheets.Add(Aspose.Cells.SheetType.Worksheet)
loToWB.Worksheets(loToWB.Worksheets.Count - 1).Copy(loFromWB.Worksheets(0))
loToWB.Worksheets(loToWB.Worksheets.Count - 1).Name = "Hide"
loToWB.Worksheets.RemoveAt("Main")
loToWB.Worksheets.Add(Aspose.Cells.SheetType.Worksheet)
loToWB.Worksheets(loToWB.Worksheets.Count - 1).Copy(loToWB.Worksheets("Hide"))
loToWB.Worksheets(loToWB.Worksheets.Count - 1).Name = "Main"
loToWB.Worksheets("Hide").SetVisible(False, True)
loToWB.Save(fileName:="C:\Temp\ToWB.xlsx", saveFormat:=Aspose.Cells.FileFormatType.Xlsx)
loToWB = Nothing
And here are the workbooks mentioned.
NamedRangesIssue.zip (17.9 KB)
Any assistance you can give would be greatly appreciated.
Thanks, Julie
