how to link CustomDocumentProperties to a Named Range (harder than u might think...)

[i-Zapp]

So it's pretty straightforward to link a cell value TO a custom document property, but that only provides the functionality of WRITING the property field. What I'm more interested in is how Excel takes a value stored in a document field and passes it into a range value (the opposite direction)...

At my workplace we use a document management system (ETQ) that somehow is able to edit a handful of custom document fields. Those fields are NOT "linked to cell content". Interestingly (and inexplicably) it works! Meaning that ETQ modifies the properties, and when the Excel document is opened those properties magically appear in the cells with associated names. How?????

what's even more perplexing is that there's no standard convention between the naming of the properties and the ranges. If there were, you could proffer that Excel somehow "knows" to connect the property to the range. But that's not the case.

What kind of magic is going on here? Any ideas????

document property...........named range
DOCWORK_TITLE...............DOCWORK_TITLE
ETQ$Number.....................ETQ_NUMBER
ETQ$REVISION..................ETQ_REVISION
ETQ$APPROVERS...............ETQ_APPROVERS
ETQ$EFFECTIVE_DATE........ETQEFFECTIVE_DATE

2022-05-04_15-48-51.png

[carlmon]

I guarantee somebody on here could figure it out with an example workbook*.

[i-Zapp]

ok, i attached a file. But i don't think it's much use unless you have the ability to change the Custom Document Properties without opening Excel.
if you CAN, then i would very much be interested!
(note that 'somehow' Excel is able to absorb those values into the associated named ranges)
this topic is posted here instead of general because i know here is where all teh smart people hang out!

[cytop]

If you add a .ZIP extension to the filename, then double click to open the ZIP you will see a folder called 'docprops'. In that folder is a file named 'custom.xml'. That file contains XML to define the various properties. Can't display here as the forum blocks XML in posts, seemingly.

OK, I know you said 'without opening' but you can edit the Custom properties with VBA (in another workbook) by copying the file to a ZIP, extracting the various bits and pieces and editing as you want. I do this to hack the VBProject.bin file in a workbook to display the 'Project is unviewable' message if someone tries to get to the code but it would be as easy as pie to rename the workbook, open the zip, extract cusom.xml and just use Notepad to edit. Save the edits and simply copy the file back into the folder then close the open zip and rename the workbook removing the .ZIP extension.

Talking of adding an extension ... I extracted the XML file and renamed it as .TXT, no problem uploading that.

[i-Zapp]

oh this XML stuff has me intrigued!
how do i learn more? (the internet is huge)
more specifically though, by examining that XML i do see the references to the properties (i guess not surprisingly)
but I'm still left scratching my head on how those values get pushed into the Named Ranges (INSIDE the workbook).

[cytop]

... but I'm still left scratching my head on how those values get pushed into the Named Ranges (INSIDE the workbook).

Looking at that myself but one thing is certain - it is not 'dynamic'. I edited the custom XML and changed the name to 'Fred Smith' with no difference to the content of the cell when loaded. Thinking the cell value is updated when the custom properties are created.

Still looking but I think this is a 'dumb' system, not literally, but the workbook is created with the custom props, named ranges (You can see those in the workbook.xml file in the XL folder) and values in the cells and there is no further updating via formulas (certainly there's no VBA).

[i-Zapp]

Still looking but I think this is a 'dumb' system, not literally, but the workbook is created with the custom props, named ranges (You can see those in the workbook.xml file in the XL folder) and values in the cells and there is no further updating via formulas (certainly there's no VBA).

there's def some magic to it... a 'blank' template (as attached) is uploaded into the ETQ system and the various document properties are added into the ETQ form. Then the ETQ's "save" button is pushed (super quick process) and woolah if I now open the Excel doc then all the Named Ranges are populated with properties I just entered into ETQ.

FM I say.

[cytop]

The file you attached to post #3 already has all the named ranges and custom properties defined so not sure what you mean by 'blank template'.

It is absolutely possible to edit an Excel workbook outside of Excel, and adding things like named ranges and custom properties is a simple as editing the XML files contained in the workbook so, if there's any 'magic', then it's ETQ that is the magician - but it's fairly low level stuff as there's a whole world of stuff devoted to external editing of workbooks: For example - https://docs.microsoft.com/en-us/jav...cel-js-preview (But perhaps that's not a very good example as it actually runs Excel in order to get to the XML, still ...)

[i-Zapp]

The file you attached to post #3 already has all the named ranges and custom properties defined so not sure what you mean by 'blank template'..)

sorry, what i meant by 'blank' was that the template has either blank or default values. ETQ then modifies some component(s) of the Excel file collection, which apparently also includes the XML file that contains the values for the Named Ranges.

thank you for your help, you've successfully pointed me towards a new (and likely deep) rabbit hole!

[cytop]

Can definitely say the file, as it was uploaded here, is not in any way dynamic. All of the named ranges, custom properties and various bits of text were inserted by that ETQ process. And, as a final thing, you can edit the 'sharedstrings.xml' in the xl folder to set the values that appear in the workbook:

Partial XML that, hopefully, won't be blocked. Changes that name in B2:

...:</t></si><si><t>FM-00581</t></si><si><t>Excel header test</t></si><si><t>Fred Smith</t></si><si><t>May 4, 2022</t></si></sst>