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.

Fixing dimensions of 2D shapes

I am often asked what makes Visio unique and makes it stand out from the crowd, especially in today’s online world. Well, I think there are many reasons, but one of them is the ability to create scaled drawings with parametric components of specific dimensions. This was crucial for my adoption of Visio back in…

Merging Linked Data from Similar Tables

I was recently asked how to link data from different tables but with similar column names to Visio shapes. In this case, each table has the same unique identifier, but some of the column names are the same. The problem is that the data linking matches the column name with the label of a Shape…

Smart Radio Buttons and Check Boxes in Visio

A recent project requires an interactive tutorial within Microsoft Visio desktop where a lot of the questions need a single answer using radio buttons, or multiple-choice answers using check boxes. I thought that this would be a great use of the list containers capability because the questions and answers could be part of the container…

Grid Snapping Revisited

I have previously tackled the subject of snapping to grids in Visio desktop (see https://bvisual.net/2018/06/19/really-snapping-to-grids-in-visio/ ) but a recent project required me to improve the example because it did not respond to all cursor arrow keys. The problem was that the previous solution could not understand which arrow key had been clicked, therefore it did…

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…