COM Exception when trying to create Named Ranges on Excel

[XavAM]

Hi!

I am facing a weird bug and I cannot figure out a workaround : I am trying to create a Named Range through NetOffice Functions but I get a COMException (HRESULT 0x800A03EC) whenever I invoke the method Workbook.Names.Add("myExampleName") or one of its overload.

Calling the Names.Add method on Worksheet object leads to the exact same result.

I am using last Nuget Package NetOfficeFw.Core 1.7.4.11 and NetOfficeFw.Excel 1.7.4.11, my Excel version is Office 365, 16.0.13001 64 bits.

Am I doing anything wrong?

Thanks for your help and for maintaining this fantastic tool 😄

[jozefizso]

Hi XavAM, do you set the refersTo argument of the Add(string name, string refersTo) method?

This sample works for me:

var app = new Application();
var workbook = app.Workbooks.Add();
var worksheet = (Worksheet)workbook.Worksheets[1];

worksheet.Names.Add("myName", "=Sheet1!R1C1");

workbook.Close(saveChanges: false);
app.Quit();

Sample code: https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.names

Names.Add method documentation: https://docs.microsoft.com/en-us/office/vba/api/excel.names.add

[jozefizso]

And thanks for updating to latest NetOfficeFw.Excel package, as the one you have used before - NetOffice.Excel 1.7.4.4 is a rogue package which does not contain correct NetOffice release.

[XavAM]

Hi @jozefizso ,

Thanks for your answer.

Yes I eventually figured out that it was not the correct package. To be fair, it took me a while to figure out. Despite it is written clearly on your website, it is not that obvious when you are only reading Github. May I suggest that you add this info on top of the Readme.md ? Should I be worried to have used for many months the rogue Package (is there any malware in the code) ?

Unfortunately the snippet you sent doesn't work for me, I get the exact same COMException. And, I am quite surprised that it worked since the refersTo is supposed to be in a A1-style notation., R1C1 should be used with the refersToR1C1 parameter (according to the doc). Maybe it's quite flexible, lucky you :-)

Which version of Excel are you using?

Here is a link to an opened StackOverflow question I raised this morning.

[jozefizso]

I have MS Excel 365 (Version 2006) in English.

Strangly, the code does not fail for me even when I use incorrect sheet reference in the second parameter (eg. worksheet.Names.Add("myName", "=Sheet2!R1C1");)

Here is sample document created by the code: Doc_132392314867510773.xlsx

[jozefizso]

Is your document in the XLS or XLSX format?

This answer suggests the issue may be caused by the XLS format: https://stackoverflow.com/a/7108420/67420

[jozefizso]

I tried it with absolute range from your code and it works in my Excel.

https://gist.github.com/jozefizso/8fb84c3f6d43c1151822d80b9dd63f58

[jozefizso]

Another issue with named ranges was reported here (with the same exception code): https://github.com/NetOfficeFw/NetOffice/issues/228

But I was able to reproduce that issue only when I used incorrect reference to a Sheet.

[jozefizso]

Yes I eventually figured out that it was not the correct package. To be fair, it took me a while to figure out. Despite it is written clearly on your website, it is not that obvious when you are only reading Github. May I suggest that you add this info on top of the Readme.md ? Should I be worried to have used for many months the rogue Package (is there any malware in the code) ?

Thanks a great suggestion, I will add it to the README file.

Regarding rogue packages: the owner (let call him Caio) of the NetOffice nuget packages did not want to communicate with the NetOffice author, we had to release new versions under new name (hence NetOfficeFw) and the release at the time was 1.7.4.4. After this release from official code author and maintaner, Caio released its own NetOffice 1.7.4.4 packages which contain old 1.7.3 assemblies and he remove all older versions from NuGet server. This was made to make it look like the https://github.com/netoffice project is still maintained.

There is no malicious code in that package, it is just to make NetOfficeFw packages have worse search results in NuGet.

OpenSourceDrama

[XavAM]

Hello @jozefizso ,

Thanks for your detailed answers, I will have a look to the resources you sent.

I tried this morning to run your snippet in a dedicated standalone project, and it actually works! So it seems that this really specific issue comes from an interference with the rest of the actual project. Which is quite weird because all other NetOffice Functions work perfectly.

The overall project is an Excel-DNA powered Plug-In, have you ever heard of any conflict with this framework?

The main application object is instantiated with this line of code :

I will strip down the whole project until I find what is creating this conflict, and comes back to you if I find anything 😄

[jozefizso]

I'm not familiar with the Excel-DNA project. There could be potential issue in a sense if Excel-DNA changes some Excel behavior (eg. some users reported that changing Application.Visible property may cause HRESULT 0x800A03EC when calling some Excel API).

[XavAM]

Hello @jozefizso ,

Actually, the root cause of this issue was not the method itself, but when the method was called.

I was trying to create Named Ranges from within a Excel-DNA UDF Function. But Excel considers this operation as unauthorized at this time, along with other worksheet/data manipulation, and that's why the COM Exception was raised.

Thanks for your kind support!

[jozefizso]

Thanks for the information.