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 Data row label, so the shared columns were updated with the values from the last linked data recordset. In this article I present a simple way that this restriction can be overcome.
Data Visualizer (DV) in Visio Plan 2 (Data | Create from Data | Create ) is great because it provides a way of automatically creating a diagram from data, but it also prevents some of the other data-linking features in Visio from being used. This is because DV wants to take control of the data in the shapes to provide refreshes from the data source or send updates to the data source. This can be very annoying if you use DV to create the initial layout, then you enhance this, so it looks just perfect, and then you just want to update some of the data without affecting the layout. DV may re-layout your carefully crafted diagram, even if there are no updates to the data that should do that. Also, you may want to integrate data from other sources that were not available or accessible to the table or query used for by Data Visualizer. This is when you may want to use Data | External Data | Custom Import which also provides a much greater number of data source types. In this article, you will learn how you can do this manually, or automate it with the help of a little VBA code (or similar).
In this example, I have used table of personnel that I have used before, but added an extra column, Shape Type, that simply has the value Rectangle, which is simply the name of a simple Master shape that I have added to my macro-enabled stencil, ExDataLinker.vssm.
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 possible … with a little preparation!
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, and the plea was how to make it work! Well, I experimented with this, and found that DV does not really work well in this way, and then proposed an alternative approach, which I will explain in this article.
First, I will show how it fails by setting up an Excel table that does contains multiple connections, but fails to update the labels consistently.
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 Visualization with Microsoft Visio Professional 2016, but I mostly write C# code in VSTO add-ins, so I thought it would be useful to demonstrate how easy it is to create data links by dropping a shape, and by adding links to an existing shape. The book, by the way, is still relevant for the current Visio Professional and Visio Plan 2 editions!
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.
[Read more…] about Update any Visio ShapeSheet cell with External Data
