Some DDE formulas in Excel documents are rewritten when the file is saved and reloaded. The item part of the formula is prefixed with _xlbgnm, for example:
=App|Topic!ABC1
is changed to:
=App|Topic!_xlbgnm.ABC1
This seems to happens:
.xslx files only, the old .xls format isn't affected[A-Za-z]{1,3}[0-9]+)Why does it happen, and is there a way to avoid it without changing the item?
Excel uses various _xl* prefixes in conjunction with elements that were created in the future relative to the version of Excel or to the file format version a file is saved in.
The most well-known is _xlfn prefix. It is used for functions that do not exist in, say, an earlier version of Excel that is used to open the file.
So what is happening is precisely that, your items may exist in the data type, but are created in it in a way that is not available to an earlier version. It cannot be accessed as the method is not available and so you get the prefix prepended to the function.
I have not found the meaning of the four letters "bgnm" so I cannot give you deeper insight and satisfaction, however, I can say the last two letters, "nm", mean Name in Excel's usage (for instance, many built-in defined names are built with a _xlnm prefix), leaving only the "bg" portion standing between complete clarity and you.
However, the basis for the problem is unfixable using the particular data type you are sourcing. I don't use them, so I cannot say, but quite possibly there is an older version of this particular data type that you can access instead. In that case, its creation method may not use the particular future technique or tool that is making the data type and you may then never have to see this happen again.
Or, of course, the current data type may have documentation that warns of the issue and how to solve it.
Of course, common sense says that with "Name" as part of the term, the particular item names are unavailable, BUT not that they do not exist or that the data type does not include an alternate name for the item which does exist in it. So it may be a matter of simply using a different name set. It could be as easy as that.
These _xlbgnm Names are caused by saving older xls files to the modern format. They are created as soon as you save those files as xlsx or xlsm AND they contain names which in the modern version of Excel resembles a cell address.
In "old" Excel a name like ABC1 was permitted as there was no column ABC. In modern Excel columns range up to XFD so ABC1 is a valid cell address and hence not permitted as a range name. To ensure the file still produces correct results Excel adds hidden _xlbgnm range names for those names. bgnm stands for Big Grid Name.
No, I'm not saying Excel reserves the item names matching that RegEx expression for a future use.
I'm saying that MS seems to use expressions of the _xl........ type (fill in the blank so-to-speak with a meaningful (to someone there) abbreviation, so one such as "_xlfn" where "fn" means the failure of compatibility is "function"-related) to indicate a failure of compatibility. (Also, they do this MS Office-wide, hence the start of "xl" indicating an Excel issue rather than a Word or Access or PPT or... issue.)
That compatibility looks like it would usually, and understandably, be "backwards compatibility" hence one might make a spreadsheet in current Excel and use ARRATOTEXT(), save as an XLSX file, then fire up Excel 2003 and open the file. It would maintain the last saved value until the cell underwent calculation at which time it would return the #NAME? error. But from the moment of opening the file, the function would have been prefixed and would have read: _xlfnARRAYTOTEXT(....
I say "understandably" as MS does not, and most folks might not want it anyway, write updates to old versions to take their new functions. So there's what? At least 13 versions in the wild? Actually, that was a rough count. The file structure on my computer includes "Microsoft Office 15" in it and I'm sure I've seen "16" somewhere so maybe a minimum of 16 versions, 15 of which are not getting new functions added?
However, your question and some things I've found suggest it includes forward compatibility in the sense of "we used to do some things these ways and still do some of those ways, but have dropped several of them and added some new ways."
Working in an XLS file, but not in the XLSX world suggests they did thing a way, or ways, DDE-wise, and what you did was one of them. Then an update came along some year and they had dropped that method though not entirely.
So you write it they way you always did (may have been other ways to accomplish it, but like everyone, you have a default way), and if Excel sees the file to be XLS it expects that as a way of doing things. So it allows it and things work. But opening as an XLSX it knows that was no longer allowed and does not have the program branching available to it when opening the file so while the programming is, obnoxiously, actually present, it knows that it simply isn't of interest since it was an XLSX file and will not use it.
There could be MANY reasons for such. A couple that come right off the top of my head are:
That method of doing things conflicts with something new and important, hence salted widely into the new code, widely enough that the task of making sure every bit of it and all the bits those bits affect do not break if this method is still allowed was too daunting so they wrote a small bit of code to exclude acting upon the method if present in an XLS**X* file. Since there are no conflicts if opened in the versions an XLS file would be expected to be routinely used, there was no reason to write another bit of code to exclude it when the source was an XLS file.
There were security issues with the method. When revamping for a new version they went to the work of a whole new format (XLSX) and carefully excluded the method which accomplished removing the security issues. However, hundreds of billions of XLS spreadsheets were still out there and still needed to work. Could exclude the method for them too, in newer versions (can't affect the older ones, and don't care to), but that's extra work and might generate a LOT of hate. So... no.
So in the end, it is the bg in the middle that is enigmatic. (And that could definitely point to a reason!). But it is clear that it is saying there is a compatibility issue.
Logic says it is with the method of accomplishing the DDE or equivalent. Selecting a different method of implementing the DDE call or its equivalent functionality is the way forward. Here it means you doing that directly. For someone else, perhaps using a Data Type, it means doing it indirectly by choosing a different, perhaps newer (and so updated knowing this issue) Data Type source.
But what it does NOT mean, in any way, shape, or form mean, none at all, can't really emphasize this enough: in no way at all, is that the function itself, more particularly its physical representation, is the issue. So the "ABC1" is not itself the issue. The method of invoking DDE preceding it is the issue.
