Most of my posts have been about using Visio as a data consumer by linking Visio shapes to data, but Visio can be the best place to create some data from. For example, an office layout can have personnel or equipment shapes within a workstation area, or space shapes are used to define areas which are used for calculating the space usage chargeback reports. There are non-facilities management related examples too, as the only consideration is what is the best way to create relationships, measure lengths, or calculate areas. These are all tasks that are best done by just moving, resizing, connecting or containing shapes interactively in the Visio interface. In this article I will explain how Microsoft Flow can be used update a data source simply from a Visio document.
I have been updating data sources from Visio for years, using custom code or with the old Database Wizard. In fact, I was a beta tester for the Database Wizard back in 1996/7, and used it successfully for many years, but it has limitations, not least because it uses ODBC and there can only be one data source per document. More recently, I have relied on custom code, usually in the form of a C# VSTO add-in.
The world has moved on, and we have an ever-growing cloud-based toolset from Microsoft using Office365, Dynamics365, PowerBI, PowerApps, Microsoft Flow and Azure. These are really cool democratizing technologies that can provide the ability for power users in a department to create business-useful applications that others within their teams can use without any extensive training. Therefore, I decided to look into an easy way to create and update data from a Visio diagram using these technologies.
Looking at the current out-of-the-box Visio desktop application, the easiest no-code method of extracting data from a diagram is the Shape Reports (see Data Import and Export Features in Visio 2016 (and 2013) ), which can be found on the Review tab. This can be used to read the shapes on a page, or pages, and export a table of values of Shape Data, User-defined Cells, size, position, and more. See Changing Embedded Visio Report Column Headers for example.
For this example, I created a simple drawing in Visio using the Space shape because it has some pre-defined Shape Data rows, and it calculates the area automatically whenever the shape is resized, or has its vertices edit. Perfect for space chargeback, and of course, my Visio page was drawn with a scale.
I prepared a list in SharePoint to receive some of the Shape Data and User-defined Cells (necessary for the visFMAreaDisplay values in my example).
The key app that makes all of the rest possible is Microsoft Flow. It provides the ability to trigger actions from an event, such as a PowerApps button press, an email received, or, as I wanted in this example, a OneDrive for Business file being updated.
I created a flow, Insert Areas on Update, that is triggered whenever a specific Excel file, SpaceReport.xlsx, is updated on my OneDrive for Business. The flow deletes all of the rows in this list (later I will add conditions), and then inserts an item for each row in finds in the table, named Table1, that is in the Excel file. Finally, it sends me an email to tell me that it is done.
All the Visio user has to do is:
- Run a selected Shape Report to Excel
- Format the table from the second row
- Save the Excel file to a specific folder by overwriting the previous one.
Microsoft Flow does the rest! This is a very simple action to do, and it provides a often useful sanity check when the Excel report pops up before saving. It can also provide an audit trail of updates done by authorised users.
I have not yet put in much niceties, such as error conditions, copying verified data into a better structured list, or creating a log, but this has a lot of potential!
I will be expanding this into a demonstration that I will present at Microsoft Ignite (#MSIgnite / @MS_Ignite ) at the end of September in Orlando. Come and see me there!