Dear all, What's the best practice to store the named range information?

[kissinger chen]

Dear all, What's the best practice to store the named range information?

The background is: We retrieve data from web service according to some parameters, such as data source, start date and end date.  We retrieve the data to a named range, such as A1:D10. The User can refresh the named range later, or share the workbook with his co-workers.

Currently we store the name of named range, and other parameters, such as data source, start date and end date to the sheet custom property. Later when the workbook is opened again, we read the named range's name, data source, start date and end date from custom property. So the end user can refresh the data of the named range when needed.

Definitely we cannot store the named range address(R1C1) since user may insert/delete rows/columns, so the named range address will be changed. then we have to store the name of named range to the custom property.

But the problem is: the name of named range can also be changed. User can click Formulas --> Name manager to change the name. Currently we have to educate our end user do not do it. But absolutely this is not the best solution.

So could you give me some suggestion? What's the best practice to store the named range information and its associated information? Thanks.

[Alan Stubbs]

Hi Kissinger,

You can make Names Hidden so that they cannot be seen or edited in the Name manager e.g. currentWorkbook.Names.Add(rangeName, range, false).  This still doesn't stop someone writing some VBA and changing them that way though.

Depending on what exactly you need to do - serializing the data to CustomXMLParts may work well for you, in combination with the Named Range to identify the location.

Alan

[Patrick O'Beirne]

If your concern is to check whether the address is changed, I'd agree
with Alan's suggestion.
At the time the release version is prepared, copy the names definitions
into CustomXML parts which are not accessible via the UI.
On the start of a process, compare the two.

[kissinger chen]

en, Hidden named range is a solution. And it seems works.

Any another idea? Thanks.

[kissinger chen]

On Stackoverflow someone said we can protect the sheet so user cannot edit the name. It looks a closest solution.
http://stackoverflow.com/questions/37107405/whats-the-best-way-to-store-named-range-information

[Alan Stubbs]

It's hard to say without knowing more about what you are trying to achieve but it sounds like you are creating your own "tables" in Excel based on some query parameters - which you want to store and be able to refresh on demand.  I've done something very similar using hidden names as the link back to Excel and storing the query parameters and other table info in an object which is then serialized to CustomXMLParts.  It can then be retrieved and deserialized when you open the workbook so that you have access to all the query parameters again for refreshing, etc.  There are various details you need to consider around keeping track of the stored table info - if the user moves the named range, for example, or deletes it.

If you can't add any new named ranges to the sheet - as suggested in the StackOverflow answer - that isn't going to be very user-friendly if someone is building a complex report around your named-range data.

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/U8qeOpHiuI8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[kissinger chen]

Thanks for you reply.

Yes, As you said, I want to create my own tables based on some query parameters. So that user can refresh the named range later.

Yes, from UX perspective, it is not user-friendly to disable the new named range button. So here the hidden named range is the best solution.

I know there are various details to consider. For example, two hidden named range may intersect, that means one cell may be included into multiple named range.
Then when user select the cell, it is hard to finger out which named range should be chosen. what's your suggestion for this situation? Thanks.

[Alan Stubbs]

Don't allow it!  Is most probably the correct solution - i.e. when trying to insert the second range, check for overlap with existing ranges in the sheet.  Then maybe provide options for relocating it or force relocation or cancel the insert - depending on what you prefer.  Slightly trickier is if your range size depends on the data downloaded - i.e. you don't know the table size initially - then cancelling seems an unfriendly option so some kind of relocation option is required e.g. insert n cols (at the risk of breaking other data structures in the worksheet) or simply ask the user to pick a new location and then re-evaluate for its suitability.

If you've done all this, probably worth checking too that you are not over-writing any other user data when you insert any table - especially if you are creating tables over large numbers of rows/columns.

There is a data table object built into Excel/COM too.  I've not used them myself and I don't think they'll help in any way with the collision handling, etc - but maybe worth looking at.  I can't remember right now what they are called precisely.

[Patrick O'Beirne]

That would be QueryTables.

See Destination property :
https://msdn.microsoft.com/en-us/library/office/ff198271.aspx

(Data Tables are on the What-If tab, not relevant here)

[kissinger chen]

Thanks so much. Thanks for you suggestion. We should not let range overlap. We should do checking before inserting new range.

Yes, that's query table. We use it before but now we do not use it.

[kissinger chen]

Hi Alan,

Another question: for named range, we can use hidden named range.
But if user want the result to be a table, which is actually a list object(https://msdn.microsoft.com/en-us/library/office/ff197604.aspx).
Since there is no hidden table, in this case what should we do?

Thanks.

On Monday, 9 May 2016 12:22:16 UTC-7, Alan Stubbs wrote:

[Alan Stubbs]

Hi Kissinger,

I don't fully understand what you are trying to do but the Named Range isn't hidden either - the Name associated with it is but the Range itself is visible.  So I guess you maybe want to associate a Hidden Name with the ListObject's Table Range - see one of Range, DataBodyRange, HeaderRowRange at https://msdn.microsoft.com/en-us/library/office/dn300900.aspx - using Names.Add(yourRangeName, rangeOfListObject, false) as above.

I haven't worked with ListObjects but I expect this will work - depending as well on what you are trying to achieve overall.  You should definitely test that the Range you end up using works as you expect when you move the ListObject around, etc.

Alan