Automate Insert Property in Excel

Question

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_PG2B2A313564C2446C89941292831B3DCBn 1 
 For record 2 it will be =MFiles_PG2B2A313564C2446C89941292831B3DCBn 2 
 
 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.

StuartGeers · Answer

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)

Automate Insert Property in Excel

Top Replies