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.

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…

Pushing Data Visualizer in Visio to the limits!

Regular readers of my blog will know that I like to use the Data Visualizer (DV) in Visio Plan 2, but I recently tried to help a user who really decided to push it to the limits. In this scenario, there were multiple connections, but with different labels, being created between the same flowchart shapes,…

Linking Data to Visio Shapes in Code

One of the most useful capabilities of Visio Professional and Visio Plan 2 is to link external data to shapes and have them refreshed by changes in the data source. So, many of my solutions involve writing code to make these links, and they are covered with some VBA examples in my book, Mastering Data…

Visio Shape Report Converter Excel add-in available

I am delighted to announce that I have just had my first Excel add-in accepted in the Office Add-ins Store! It is called Shape Report Converter because all it does is convert a Shape Report converted from Visio for Desktop to a modern Excel table with one click! It isn’t a complicated add-in, and it…

A Multi-Time Zone Clock for Visio

I wrote a post about making a clock face in Visio fifteen years ago, but a reader recently asked about displaying multiple time zones. Well, I have previously written about time zones in Visio, so I accepted the challenge to improve upon my earlier work. (more…)

Understanding Morse Clicks with Visio

A few years ago, I wrote an article about messaging and encryption inspired by a visit to the National Museum of Computing in the UK. I developed a Morse Click shape to demonstrate how Visio can be used to represent and learn Morse Code. However, I never published the shapes here, and my good friend…