External Data

Published on January 3, 2023 by David Parker

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, 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.

Lesson 1: Export from Excel to Visio is not the same as Import to Visio from Excel

The following screenshot was created by clicking Table Design / External Table Data / Export / Export Table to Visio Diagram… in Excel for the above table, and then choosing the Cross-Functional Flowchart (horizontal) option.

Default Export to Visio Diagram from Excel

I then chose Hierarchy / Top to Bottom the Design / Layout / Re-Layout Page gallery, and created a simple Shape Report to see the text on each Dynamic connector shape. It was then clear that I had to manually change some of the connectors to use static rather than dynamic glue so that I could see them all.

Re-laid out and Curved Connectors
Manually adjusted to see all connectors

The following screenshot, however, was created by creating a new Cross-Functional Flowchart – Data Visualizer in Visio, then choosing the above Excel table.

As before, I then chose Hierarchy / Top to Bottom the Design / Layout / Re-Layout Page gallery and created a simple Shape Report to see the text on each Dynamic connector shape. Again, it was then clear that I had to manually change some of the connectors to use static rather than dynamic glue so that I could see them all. Unfortunately, Visio sometimes gets confused about which connector should have which label, and can get it wrong, as shown below, where the same label has been duplicated.

Re-laid out and Curved Connectors
Manually adjusted to see all connectors

These errors can occur in the export from Excel too. I have also seen instances where the connector label becomes uneditable in Visio.

Therefore, my conclusion is that DV cannot handle multiple connectors between the same shapes.

Also, the template used by the export to Visio from Excel is different to the one used by import from Excel in Visio.

Lesson 2: Making Connectors Invisible is not the Same as Making Lines 100% Transparent

I added extra connection points to the Process shape so that all the connectors could be spaced out, and I edited the labels back to what they should have been, so finally I could display the connections clearly in the following screenshot.

However, this diagram needs to be used to display the alternative routes between Process shapes, and the user wanted to make some connectors invisible so that the desired connectors are shown clearly. My first thought was to move some connectors to a different layer, and then make the new layer invisible.

Unfortunately, DV automatically deleted all the connectors that were assigned to a layer that was made invisible!

Assign B connectors to new layer
Make new layer invisible
DV automatically deletes the invisible connectors

So, the alternative is to assign this new layer to a colour, and make the colour 100% transparent!

Assign Bs to new layer
Assign a transparent colour to layer
Transparent connectors remain

So, my conclusion is to never assign any DV controlled shapes to a transparent layer!

Lesson 3: Even choosing Color By Value using Data Graphic Fields causes a re-layout

There are times when I want to manually adjust the layout because DV does not do it quite right … However, if I then choose to use Data Design / Refresh Diagram or simply select a field in the Data Graphic Fields panel, then DV will automatically re-layout the diagram, even if all I wanted was to have Color by Value. This is because the Data Graphic Field row selection will try to guess what Graphic Item you may want, and this will change all the data-linked shapes to groups, if they are not already, and insert a Graphic Item sub-shape causing the bounding rectangle to be extended, and consequently forcing the layout to be re-spaced. Then, if you change it to the Color by Value that was really wanted, it is too late, the unrequired sub-shape is removed, and the re-layout is automatically performed again!

Manually adjusted layout
Select a Data Graphic Field
Changed to Color By Value

My next post will focus on a solution to overcome these issues with Data Visualizer... This will mean creating a modified Visio template to import from Excel!

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…

Teams Tuesday Podcast Recording about Visio

I am delighted to share the link to my recent demonstration and conversation with fellow MVP Peter Ward about Visio in Teams. We discussed more than just Visio … and I look a bit like Max Headroom! (more…)

Binding Visio and Excel with Data Visualizer

I re-vamped some of my content about the Visio Data Visualizer add-in for Excel in my last post, so I thought I should expand a bit more on the Data Visualizer feature in Visio for Desktop (Visio Plan 2) and Excel for Desktop. I have described it in detail in previous posts, but I never…

Editing Visio Data Visualizer in Excel

I have previously described how to use the Visio Data Visualizer add-in in Excel, but I didn’t make it clear how you can edit the diagram created if you have a Visio license. So, I have recorded three short videos to briefly explain this. (more…)

Visio Data Visualizer Tips

The Microsoft Visio product team have released a great video introduction to the Visio Data Visualizer, but even that does not reveal all of its power. So, here are some tips about using the Microsoft Visio Data Visualizer add-in in Excel. Firstly, the add-in currently offers three different types of diagram, and each have a…

Using some Visio Shape Data in Visio for the Web

Like a lot of UK TV viewers last week, I watched Quiz, a drama about Major Charles Ingram who apparently cheated his way to the jackpot in Who Wants to be a Millionaire. Some of the questions that he was asked were quoted in that show, and I was surprised how easy the last two…

Published on November 2, 2022 by David Parker

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 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!

[Read more…]

Published on April 18, 2022 by David Parker

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.

[Read more…]

Published on April 15, 2021 by David Parker

Visio 2010 MVP Session videos reprise

Back in 2012, my fellow Visio MVPs, Scott Helmers and Chris Roth, and I recorded a series of 24 videos about Visio 2010. They were first hosted on Microsoft’s web site, then they put them up on YouTube, they they got deleted :-(. Well, we have managed to retrieve them, and put them back up on YouTube in a new list :-). Most of them are still very relevant and useful!

See Visio 2010 MVP Sessions – YouTube and The Visio 2010 MVP Sessions .

Published on April 11, 2019 by David Parker

Sorry about Brexit

In these days of political uncertainty in the UK, and our position in Europe or the world, there is only one thing that I can say: Sorry about Brexit … in every main language of the EU countries!

[Read more…]

Published on March 7, 2019 by David Parker

Changing Visio Shape Size and Angle with Linked Data

I was recently laying out a computer room floor plan to scale in Visio, from survey data of the cabinets\racks within it, and it occurred to me that I could work a bit smarter than I first thought. Originally, I was going to just add lots of rectangles, size them, rotate them, and label each them with their name. Then I thought, why not use the Width, Depth and Name columns from the survey data to do most of the work for me? It was so simple to do, and only took a few minutes, so I thought I would share my methodology because it demonstrates how easy it is to work smarter with Visio….