Named ranges disappear from macro-enabled workbook when reopened after save

Aspose.Cells Product Family
1

Our application has a concept of allowing users to set up Excel template files with formulas that refer to named ranges which will only be created after the fact. While everything works fine with .xls and .xlsx files, we have problems with this when using macro-enabled .xlsm files. If a named range is used in a formula of an xlsm file when opening it with Aspose, we can add/specify that named range to the in-memory workbook object as expected, but after saving the workbook to a file the referenced named range is gone when we reopen the workbook, and the formula displays an error.

In the attached code, we expect a total of 4 named ranges to be present in the final output, with names defined in variables rangeName, rangeNameA, rangeNameB and rangeNameC. What we are seeing instead is that rangeName and rangeNameC are not present when opening the final result in Excel. Note that rangeNameC is the only one that ends up having a formula referencing it, and rangeName, while it doesn’t have a formula referencing it when the workbook was last saved with the named range data populated, it did have a formula referencing it in an earlier save, when the named range data was not yet populated.

We have reproduced this problem in the latest available version of Aspose.Cells.code.zip (1.3 KB)

2

@jcapsonbr can you please attach the source Workbook (xls, xlsx, etc.) file.

3

No source workbook needed, the attached code creates an XLSM “template”, adds the formula referencing the non-existent to-be-populated-in-the-future named range, saves the workbook, reopens it, adds data for and creates 4 named ranges, saves the workbook, reopens it, and, sadly, the 2 named ranges that were referenced are gone.

1 Like
4

@jcapsonbr,

I reproduced the issue you mentioned using your test-case. I found some named ranges disappear from macro-enabled workbook when reopened after save. We will investigate your issue in details and may share the internal mechanism regarding initialization of named ranges in the workbooks. If it is an issue, we will fix it in the APIs.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-53125

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

1 Like
5

Thank you very much, we are looking forward to getting a fix!

6

@jcapsonbr,
We are pleased to inform you that your issue has been resolved. The fix will be included in our upcoming release (Aspose. Cells v23.4) that we plan to release in the first half of April 2023. You will be notified when the next version is released.

1 Like
7

Thank you for the quick turnaround!

8

@jcapsonbr
You are welcome . Thanks for your patience.

9

The issues you have found earlier (filed as CELLSNET-53125) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

10

Thank you, we will download and test.
I just spoke to some higher-ups and it may be a while before we can renew our license.

Is there a possible workaround that you can recommend for this issue? (That doesn’t involve not using an XLSM file)

If not, is it possible to have the fix merged into 21.12 (our current version)?

11

@jcapsonbr,

For you situation, as an workaround we think you may try to change your code a bit to add those defined names into the workbook manually:

            ...
            var resultSheet = template.Worksheets.Add(resultSheetName);
            //ADD BELOW LINES
            template.Worksheets.Names[template.Worksheets.Names.Add(rangeName)].RefersTo = "#REF!";
            template.Worksheets.Names[template.Worksheets.Names.Add(rangeNameA)].RefersTo = "#REF!";
            ...
            resultSheet.Cells[0, 0].Formula = $"=INDEX({rangeName}, 1, 1)";
            ...
12

If the workaround shared by @johnson.shi is not applicable, I am afraid, there is no other alternative but to upgrade to latest version. Please note, we cannot include fixes in the older versions of the API as the fixes are based on latest APIs set only.

13

Many thanks for this suggestion! Since the templates in question are created by our clients directly in Excel, rather than in code, we couldn’t find any quick or easy way to set a named range to point to “#REF!” in code. However, through some testing inspired by your suggestion, we have found that simply creating the named range in the Excel template and pointing it to ANY cell(s) is sufficient to get this to work. When our code runs and opens the client-created template, because the named ranges already exist, our code updates them to the correct values and they don’t end up getting deleted when our code saves the worksheet. The end result is that the formulas that point to the updated no-longer-deleted named ranges work. Of course this means our client will have to do some extra up front work, adding named ranges with temporary references for all the tables that will be added at run time. This should get them by until we are able to get latest Aspose.Cells purchased, tested and integrated into a production release.

Thanks again!

14

@jcapsonbr,
Thank you for your feedback. If you have any questions, please feel free to contact us.