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.
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…
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…
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…)
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…
I have only been developing Visio solutions for 25 years, but I got a surprise this week when I was trying to understand why I couldn’t update the value of a Shape Data row of multiple selected shapes. It is something that I often do, so it took me a while to understand why I…
I was going to title this “When is a truth not a truth?” because I have found that Visio can sometimes refuse to apparently update the displayed value in a cell, even though it has been changed. However, this was, in this instance, down to a misunderstanding of the formula evaluation events on my part.…