We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Named Ranges lost when copying into existing workbook

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

@t1jsw

Thanks for using Aspose APIs.

We were able to observe this issue as shown in the following screenshot and as per your sample code and sample Excel files. We have logged the issue in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-46098 - Named Ranges lost when copying into existing workbook

Screenshot of Error:

@t1jsw

Thanks for using Aspose APIs.

This is to inform you that we have fixed your issue CELLSNET-46098 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

@t1jsw

Thanks for using Aspose APIs.

Please download and try the following fix for your issue CELLSNET-46098 and let us know your feedback.

The issues you have found earlier (filed as CELLSNET-46098) have been fixed in this update. Please also check the document/article for your reference: https://docs.aspose.com/display/cellsnet/Installation

Thanks Shakeel.

Sorry for the slow response. I have just upgraded to 18.6 and can confirm this issue is resolved. Thank you for all your help.

@t1jsw

Thanks for your feedback and using Aspose APIs.

It is good to know that your issue is resolved with the provided fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.