In this article by David J Parker, the author of the book Mastering Data Visualization with Microsoft Visio Professional 2016, discusses about that Microsoft introduced the current data-linking feature in the Professional edition of Visio Professional 2007. This feature is better than the database add-on that has been around since Visio 4 because it has greater importing capabilities and is part of the core product with its own API. This provides the Visio user with a simple method of surfacing data from a variety of data sources, and it gives the power user (or developer) the ability to create productivity enhancements in code.
(For more resources related to this topic, see here.)
Once data is imported in Visio, the rows of data can be linked to shapes and then displayed visually, or be used to automatically create hyperlinks. Moreover, if the data is edited outside of Visio, then the data in the Visio shapes can be refreshed, so the shapes reflect the updated data. This can be done in the Visio client, but some data sources can also refresh the data in Visio documents that are displayed in SharePoint web pages.
In this way, Visio documents truly become operational intelligence dashboards.
Some VBA knowledge will be useful, and the sample data sources are introduced in each section.
In this chapter, we shall cover the following topics:
- The new Quick Import feature
- Importing data from a variety of sources
- How to link shapes to rows of data
- Using code for more linking possibilities
A very quick introduction to importing and linking data
Visio Professional 2016 added more buttons to the Data ribbon tab, and some new Data Graphics, but the functionality has basically been the same since Visio 2007 Professional. The new additions, as seen in the following screenshot, can make this particular ribbon tab quite wide on the screen. Thank goodness that wide screens have become the norm:
The process to create data-refreshable shapes in Visio is simply as follows:
- Import data as recordsets.
- Link rows of data to shapes.
- Make the shapes display the data.
- Use any hyperlinks that have been created automatically.
The Quick Import tool introduced in Visio 2016 Professional attempts to merge the first three steps into one, but it rarely gets it perfectly, and it is only for simple Excel data sources. Therefore, it is necessary to learn how to use the Custom Import feature properly.
Knowing when to use the Quick Import tool
The Data | External Data | Quick Import button is new in Visio 2016 Professional. It is part of the Visio API, so it cannot be called in code. This is not a great problem because it is only a wrapper for some of the actions that can be done in code anyway.
This feature can only use an Excel workbook, but fortunately Visio installs a sample OrgData.xls file in the Visio Content\<LCID> folder. The LCID (Location Code Identifier) for US English is 1033, as shown in the following screenshot:
The screenshot shows a Visio Professional 2016 32-bit installation is on a Windows 10 64-bit laptop. Therefore, the Office16 applications are installed in the Program Files (x86)\root folder. It would just be Program Files\root if the 64-bit version of Office was installed. It is not possible to install a different bit version of Visio than the rest of the Office applications. There is no root folder in previous versions of Office, but the rest of the path is the same.
The full path on this laptop is C:\Program Files (x86)\Microsoft Office\root\Office16\Visio Content\1033\ORGDATA.XLS, but it is best to copy this file to a folder where it can be edited. It is surprising that the Excel workbook is in the old binary format, but it is a simple process to open and save it in the new Open Packaging Convention file format with an xlsx extension.
Importing to shapes without existing Shape Data rows
The following example contains three Person shapes from the Work Flow Objects stencil, and each one contains the names of a person’s name, spelt exactly the same as in the key column on the Excel worksheet. It is not case sensitive, and it does not matter whether there are leading or trailing spaces in the text.
When the Quick Import button is pressed, a dialog opens up to show the progress of the stages that the wizard feature is going through, as shown in the following screenshot:
If the workbook contains more than one table of data, the user is prompted to select the range of cells within the workbook.
When the process is complete, each of the Person shapes contains all of the data from the row in the External Data recordset, where the text matches the Name column, as shown in the following screenshot:
The linked rows in the External Data window also display a chain icon, and the right-click menu has many actions, such as selecting the Linked Shapes for a row. Conversely, each shape now contains a right-mouse menu action to select the linked row in an External Data recordset.
The Quick Import feature also adds some default data graphics to each shape, which will be ignored in this chapter because it is explored in detail in chapter 4, Using the Built-in Data Graphics.
Note that the recordset in the External Data window is named Sheet1$A1:H52. This is not perfect, but the user can rename it through the right mouse menu actions of the tab. The Properties dialog, as seen in the following screenshot:
The user can also choose what to do if a data link is added to a shape that already has one.
A shape can be linked to a single row in multiple recordsets, and a single row can be linked to multiple shapes in a document, or even on the same page. However, a shape cannot be linked to more than one row in the same recordset.
Importing to shapes with existing Shape Data rows
The Person shape from the Resources stencil has been used in the following example, and as earlier, each shape has the name text. However, in this case, there are some existing Shape Data rows:
When the Quick Import feature is run, the data is linked to each shape where the text matches the Name column value.
This feature has unfortunately created a problem this time because the Phone Number, E-mail Alias, and Manager Shape Data rows have remained empty, but the superfluous Telephone, E-mail, and Reports_To have been added.
The solution is to edit the column headers in the worksheet to match the existing Shape Data row labels, as shown in the following screenshot:
Then, when Quick Import is used again, the column headers will match the Shape Data row names, and the data will be automatically cached into the correct places, as shown in the following screenshot:
Using the Custom Import feature
The user has more control using the Custom Import button on the Data | External Data ribbon tab. This button was called Link Data to Shapes in the previous versions of Visio. In either case, the action opens the Data Selector dialog, as shown in the following screenshot:
Each of these data sources will be explained in this chapter, along with the two data sources that are not available in the UI (namely XML files and SQL Server Stored Procedures).
This article has gone through the many different sources for importing data in Visio and has shown how each can be done.
Resources for Article:
- Overview of Process Management in Microsoft Visio 2013[article]
- Data Visualization[article]
- Data visualization[article]