Update any Visio ShapeSheet cell with External Data

When Microsoft introduced a new way of linking external data to Visio shapes in 2007, I initially bemoaned the inability to update anything but Shape Data row values, unlike the old database add-on that I had been using for 10 years. The new method, though, has many advantages over the old way, not least that it is part of the Visio code library, for any Visio desktop user except the Standard edition.

The smartness of Visio shapes is derived from the formulas in the ShapeSheet that lies behind everything. There is a developer interface into this ShapeSheet in every desktop edition, but there is none in any of the web editions, even though the formulas, for the most part, work perfectly well (see my series of posts ShapeSheet Functions A-Z ).

The newer methodology of linking Visio shapes to data can also be used to refresh Visio diagrams automatically, even if the Visio document is not opened in the desktop edition. The Visio documents merely need to be stored in OneDrive, SharePoint (or Teams). This makes it suitable for solution development without assuming that every consumer has a desktop license. Indeed, the Visio Data Visualizer utilizes these newer external data recordsets embedded within the Visio document.

For my example, I used the Visio Sample Database that has been installed with Visio since 1997 for demonstrating the old database linking technology. It is an old style Access database (mdb), and can be found in a sub-folder of the Visio installation folder. I simply used the Data \ External Data \ Custom Import command to import the Bolts – Square Head table.

This will display the selected table in the External Data window in Visio, and simply dragging and dropping a row from this recordset onto the page, will automatically create a Rectangle master shape (unless you had another master selected), and adds the data columns as Shape Data rows, and usually displays some of this data as Data Graphics.

Well, the automatically applied data graphics can be switched off by unticking the Data \ Advanced Data Linking \ Advanced Data Graphics \ Apply after Linking Data to Shapes option, but notice that the shape size has not changed to reflect the values in the Width and Height columns.

This can be fixed by entering a SETATREF(…) function into the Shape Data value cells whose values you want to re-direct to another cell. In this case the formulas =SETATREF(Width) and =SETATREF(Height), are entered into the Prop._visDM_Width.Value and Prop._visDM_Height.Value cells.

The Width and Height of the shape is then automatically set to the desired values, and if a different row from the recordset is dragged and dropped onto the same shape, then the values are replaced, and the shape automatically changes size.

All that is required now is to drag and drop this shape on to a stencil to create a master shape, which I called Bolt Head – Square Head. Now this master can be used to create multiple shape copies by simply dragging and dropping from multiple rows from the External Data window, whilst the Bolt – Square Head master is selected in the stencil.

The values in the Width and Height Shape Data rows can also be manually entered using the Shape Data window, but, if that is not required, then their Invisible cell should have =TRUE in them. This will hide them from the user-interface in the Shape Data window.

However, if you want to also prevent the user from manually resizing the shape with the mouse or with the Size & Position window, then simply enter =1 into the LockWidth and LockHeight cells in the ShapeSheet. The shape Width and Height can now only be updated from the source data columns.

Notice that I also entered =TRUE into the NoObjHandles cell, to make the appearance cleaner.

Of course, Width and Height are not the only cells that can be automatically updated in this way, and the entries can be formulas rather than values.

When is a Visio Callout not a Callout?

I have been a Visio user/developer since the mid-1990’s and seen the word “callout” used as part of the name of many master shapes in Visio. The images below show five ways that the term “callout” has been applied to the name of Visio master shapes. Generally, each evolution has been an advance on the…

Using Visio Color by Value on Connectors

Data Graphics in Visio Plan 2 and Visio Professional is great, but it only enables us to use them with 2D shapes in Visio, i.e. not on connectors. So, what if you want to change the line colour of the connectors between the 2D shapes because of the data flowing between them? Well, it is…

More Parsing XML Data in Visio Shapes

My last article looked at parsing an XML string with a known structure and order of elements and attributes. This can be acceptable in some scenarios, but what if the elements or attributes are in a different order? Then it is necessary to use the element and attribute names rather than their index position. This…

Highlighting dirty text in Visio shapes

I suppose I should explain what I mean by dirty text first 🙂 I often display the value of a Shape Data row in Visio text, but sometimes the solution requires that the value is also editable. This is ok if the client accepts that all editing is done with the Shape Data window or…

Interactive Demographics of the European Union in Visio

I was recently contacted by a reader, Stanley M. Max (Towson University lecturer), who had started creating a Visio map of the 27 countries in the European Union in 2020, along with the demographics culled from Wikipedia. He wanted to know if the map and stats could be combined to make a more appealing presentation.…

Pushing Data Visualizer in Visio beyond its limits

My last post was about some of the lessons learnt when trying to push Data Visualizer to its limits, but this one has some ways of overcoming these limitations. The main lesson learnt is that DV binds the shapes within the DV container shape, CFF Data Visualizer, and controls some of the ShapeSheet cells that…