Guest Post by Dany Hoter
After I published a post about manipulating relationships, Rob suggested that I take a step back and cover the entire scope of what’s possible with the object model.
Can you build a model from scratch? Can you add a new table to an existing table? Can you add calculated columns? What about calculated measures? , Can you change a connection for an existing table in the model?
The short answer to these questions is Yes, Yes, No, No, Yes
The longer version is the rest of this post. Everything in this post is NOT possible in Excel 2010 – this stuff works in 2013 only.
The object model consists of the following elements:
The only property that I found useful in this list is ModelRelationships collection which I used extensively in the previous post.
The ModelTables collection looks promising as it contains ModelTableColumns and could be the way to introduce new tables, new columns or even measures into the model. Unfortunately all these collections are read-only and cannot be used for adding to the model.
So how is still possible to add new tables or even to start a model from scratch?
It all has to do with the method add2 of the Connections collection.