Automate Insert Property in Excel

Hi all,

Let's say that I have this list of purchase order object in my metadata and I wanted to list them automatically inside Excel file as the M-Files template.

Currently, I need to insert property 1 by 1 on each cell.

However, I did notice that the excel command area has some pattern.

For instance, for record 1 it will be =MFiles_PG2B2A313564C2446C89941292831B3DCBn1

For record 2 it will be =MFiles_PG2B2A313564C2446C89941292831B3DCBn2

I noticed that the last number will be increasing for each property.

So I was thinking to copy paste this command and just changing the last number instead of going into the M-Files ribbon and perform insert property.

However, if I do that, it won't show the value as per screenshot below:

This can only be displayed when I manually use the insert property function eventhough the command is still the same.

I am just wondering whether is there any way to automate/minimize this action ? Or we need to do this 1 by 1. For instance, if i have 30 cell then I need to manually insert property 1 by 1.

  • depending on the number you need to do i've handled it two ways in the past. 

    use is ctrl+` to show formula after you've made one (note that might go <string>n2_<string>n3_<string> and you want to update one of the middles ones), then reformat that formula for the n2, n3, n4 etc either with concatenate or some other method, then get a list without the ='s signs (find replace, etc).  Once you have your list without ='s signs

    1) manually add them one by one in excel under Formulas -> Define Name, where the name is the "MFiles_PG2B2A313564C2446C89941292831B3DCBn#" and the Refers to is ="" (equals quote quote)., (reprogramming some hot buttons for ctrl c, ctrl v, ="" can go a long way to speed it up)

    2) use a VBA script to mass add Name Manager names (google for that)