Updating data sources from #Visio using #MSFlow … easily!

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.

This slideshow requires JavaScript.

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:

  1. Run a selected Shape Report to Excel
  2. Format the table from the second row
  3. 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!

 

One Response to “Updating data sources from #Visio using #MSFlow … easily!”

  1. #Visio custom visual preview for #PowerBI available for all! | bVisual - for people interested in Microsoft Visio Says:

    […] I wrote about using #MSFlow with #Visio ( Updating data sources from #Visio using #MSFlow … easily! ), so it is fitting to announce that the Visio custom visual for PowerBI is now available from the […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Chris Webb's BI Blog

Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

davecra.wordpress.com/

Microsoft Office 365 Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: