bVisual

Excel

Published on by

MSIgnite Sessions to Watch in Visio

Sessions2Watch.gif

Well, MS Ignite is over for another year. I planned to visit loads of sessions, but the reality was that I spent many hours on the Visio booth. Fortunately, many of them were recorded, including ours, but I don’t have time to watch over 700 videos! So, I looked through them all and picked out 50-odd, and used Get & Transform in Excel again to create suitable queries to link to Visio.

The more I use PowerQuery, the more I like it. I was able to extract the unique values in the Personas, Tags, and Products columns which each contained comma separated lists. I data-linked these values to large circles on different pages, and data-linked the Sessions to Watch to hexagonal shapes. I then wrote a little VBA code to link the circles to the hexagons with data-linked connectors. Data Graphics was applied to all shapes, including the connectors, and a legend added. Each of the Session hexagons have an automatically created hyperlink to the On-Demand Video. I used a standard radial page layout so that I had minimal manual movement shapes.
The end result is a visual dashboard by Persona, Tag and Product to open the videos for the sessions I want to watch.

[Read more…] about MSIgnite Sessions to Watch in Visio

Published on by

Visio Shape Data Linking Tips

I often use the Microsoft supplied OrgData.xls sample file to demonstrate linking to external data in Visio, however there are a few gotchas lying in wait for the unwary if you try to link to the Microsoft supplied Org Chart shapes …

In the following screenshot, I have the OrgData table showing in the External Data window and I have the ShapeSheet open for the selected Executive Belt shape. Notice that the Shape Data section rows are all black which indicates that they are inherited from the master shape. Also note that there is a Shape Data row with the label “E-Mail” in the ShapeSheet, and one called “E-mail” in the Personnel table.

image

 

[Read more…] about Visio Shape Data Linking Tips

Published on by

Updating the file path of data linked Excel tables in Visio

Link Data to Shapes in Visio (not the Standard edition though) is great for visualizing information, and I often use a multi-worksheet Excel workbook that I create locally, and then wish to re-link to a copy of the workbook elsewhere, or even in Office365. This is a simple task if there are only one or two Excel tables involved, but can become tedious when there are many. So, this blog is presents an semi-automated method of achieving this.

Firstly I have an Excel workbook that I have many worksheets with tables of data on them. I named each worksheet, and used the Format As Table command on each.

image
[Read more…] about Updating the file path of data linked Excel tables in Visio

Published on by

Linking Excel Named Ranges in Visio

I have been using worksheets and ranges in Excel with Visio’s Link Data to Shapes (in the Pro and Premium editions) for several years, but recently encountered a problem with the recently added structured references in Excel. Fortunately, with the help of my colleague John Goldsmith, we found a solution.

Normally, I create tables in Excel worksheets with the column headings in the first row, so I could use the worksheet name in the Link Data to Shapes function in Visio. However, I wanted to have an extra row above the table headers for a recent project, so I wanted to use named ranges, defined with any rows above the table headers omitted. When I went to connect these ranges using the Data Selector in Visio, I found that all of my named ranges, except one (StepIds), were missing from the list of worksheets and ranges:

image

[Read more…] about Linking Excel Named Ranges in Visio