I have been using (Microsoft) Visio for importing and exporting data since 1996, and was an early tester/adopter of the Database Wizard features in Visio 4.1 (I think). I think of Visio as a data visualization tool, yet I cannot recall seeing a comprehensive list of the areas in Visio, out-of-the-box, where you can import and export data easily. So I decided to draw a data-linked diagram of just this for Visio 2010.
I have made the files available for you at the following links:
- Visio file : http://www.visualizinginformation.com/blog/Visio 2010 Product Links.vsd
- Excel workbook : http://www.visualizinginformation.com/blog/OfficeProductLinks.xlsx
- Saved as Web version : http://www.visualizinginformation.com/blog/Visio 2010 Product Links.htm
- PDF version : http://www.visualizinginformation.com/blog/Visio 2010 Product Links.pdf
- XPS version : http://www.visualizinginformation.com/blog/Visio 2010 Product Links.xps
I built the diagram on data that I entered into an Excel workbook, although I soon realised that I could have perhaps done this better in Access or SQL Server!
I decided to split the data sources and targets into three different (product) types:
- Desktop App
- Server
- File Format
Each of the data sources and targets are automatically color-coded, using Data Graphics, according to their type:
There is a legend at the bottom of the diagram too:
The source types of import data to Visio are arranged across the top of the diagram, and the target types of export data from Visio are arranged along the bottom.
The features in Visio that utilize data are grouped by their category across the middle of the diagram:
- Business
- Data
- File / Save As
- Maps and Floor Plans
- Review
- Schedule
- View / Add-Ons / Visio Extras
Each of the Visio feature boxes contain a hyperlink that explains it in more detail.
The connectors between the data and the Visio feature are automatically color-coded too, according to the lowest edition of Visio where the feature is available.
Hopefully, you already realize that the Professional edition has all of Visio Standard, plus extra stuff. Similarly, Premium has all of Professional, and then adds more stuff, such as Visio Services and Data Validation.
What my diagram reveals is there are far more import data features than export data. This is a pity, but data updates should be done with caution, and perhaps should be identity controlled. For example, the Link Data to Shapes feature is only one way, though there are a few of us who use the connection information to create a two-way link in code.
I also used the Review / Shape Reports feature to export the relevant data to a table:
One pattern that I often need to use is consuming data from web services, and I would like to see this added to the next version of Visio, rather than having to always write the code myself.
Another common pattern that I would like to see is the automatic connection between shapes using a nominated data-linked connector … an extension to Link Data to Shapes. Whilst I am creating this wish list, I would also like to see the automatic placement of data-linked shapes into data-linked containers. Both of these wishes were achieved manually in the attached diagram.
July 6, 2011 at 6:32 am
Hi,
I am having issue with Visio while using the hyperlink when i generate the web page of the Visio file.
I am using BPMN diagram in Visio 2010 & have 5 different Visio files, each having about 30+ processes documented.
For illustration only:
Let’s say, I have created 4 files:
1st File “Plan”: which has following collapsed sub-processes: A–> B –> C?
2nd File “Planning Activities – A”: which has following tasks: A1–> A2–>A3
3rd File “Planning Activities – B”: which has following tasks: B1–> B2–>B3?
4th File “Planning Activities – C”: which has following tasks: C1–> C2–>C3?
I have linked collapsed sub-process “A” within 1st file with the sub-process worksheet saved in 2nd file to give a blown-up view of expanded sub-process “A”. Similarly I have done it for collapsed sub-process “B” as well as collapsed sub-process “C” in 1st file i.e. linked each to its blown-up view in other Visio 2010 file.
Since 1st file is the parent file, I am trying to save that as web page (*.html).
When i open the saved web page and click on the collapsed sub-process “A” it opens the Visio file for “Planning Activities – A”, instead it showing it on HTML so that I can view A1 on html and then navigate back to A-> B-> C diagram.
Ideally, when I save as the parent file in html format, data from the entire downstream Visio file should also be saved as html content with configured hyperlinks.
Appreciate if you can suggest a work around.
Regards
Rahul Gupta
August 17, 2011 at 3:26 pm
link to excel not working?
September 2, 2011 at 7:26 am
Sorry, I got the file extension wrong… fixed now.
October 28, 2011 at 9:09 am
link to excel STILL not working
October 31, 2011 at 7:36 am
it SHOULD be working now …
October 31, 2011 at 3:27 am
hi,
I am trying to create a diagram like the one you have shown as an example here. Could you post some helpful information on how you were able to create the diagram above? I am trying to work at it using the ‘link data to shapes’ feature and need to link to data from excel.
Can you please help me out??I have tried to find steps to work it out everywhere online with no luck.
Many thanks.
November 1, 2011 at 3:25 am
Hi,
Could you please post snapshots or instructions on how you created the above diagram ?
Any link where we could refer it would also be very useful.
It would be very helpful.
Thank you.
November 8, 2011 at 2:41 pm
Hopefully my other response will help?
November 4, 2011 at 4:02 am
hi
I had one question based on the example you have worked out in your post.
The visio diagram has 2 sheets:”To Product Links” and “From Product Links” while the excel provided has only 1 consolidated Product Link sheet.
Could you tell me if you were able to sort out the sheet in visio itself (if so how??) or is it a different sheet you used while creating the visio diagram?
Thank you
November 8, 2011 at 2:40 pm
I used the ProductLinks sheet as the source for both DataRecordsets, but used different filters in the Rows to Include / Select Rows… dialog.. I used a (custom) filter on the From Product and To Product fields, where they Contain the word Visio.
You can examine the query that I used with the Immediate window … the lines that start with a ? are the commands I typed in, and the following line is the response:
?Visio.ActiveDocument.DataRecordsets(4).Name
From Product Links
?Visio.ActiveDocument.DataRecordsets(3.Name
To Product Links
?Visio.ActiveDocument.DataRecordsets(4).CommandString
select * from `Product Links$` WHERE `From Product` LIKE ‘%Visio%’
?Visio.ActiveDocument.DataRecordsets(3).CommandString
select * from `Product Links$` WHERE `To Product` LIKE ‘%Visio%’
November 9, 2011 at 3:18 am
Hi David,
Thank you for so much for the detailed information. This was the a very helpful blog with data I was searching for from a long time.
I had one more question. I was trying to leverage the Web Drawing feature after creating my Visio Diagram by linking it to data from excel.
Could you explain how we can publish the web drawing on shared sites like sharepoint?
Can we do it in anyway without having the visio services enabled?
Please let me know.
Thank you again.
November 23, 2011 at 9:04 am
Visio Services gives you the ability to have a Visio drawing to be apparently updated when it is viewed in the Visio Web Drawing Share Point web part.
These updates are restricted to Shape Data values and linked Data Graphics, it does not include addition, deletion or movement of any shapes.
If you do not have Visio Services enabled then the Visio document will need to be opened in Visio to be refreshed before saving back to SharePoint (or re-publishing to any where else).
Of course, if the refresh requires adds, deletes or moves then you would need to have the Visio drawing refreshed in Visio anyway.
November 23, 2011 at 4:17 am
I’m trying to create a diagram which can update when the data is updated ie, the size of the shapes and color when the $amount is updated.
Can you please help
November 23, 2011 at 8:58 am
Firstly, you can use DataGraphics to change the color, if you want, or you can use the ShapeSheet to chnage the Color and the Size
If you have an Ellipse shape, then open the ShapeSheet (tick File / Options / Advanced / Developer Mode)
Let’s say you have a Shape Data row called Prop.Amount, with a default value of 0, and you want the shape to have a minimum width of 4 mm, then enter the following formula in Width
=GUARD(4 mm + (Prop.Amount * 0.1 mm))
and in Height:
=GUARD(Width)
You can even make the Circle grow logrithmically, if a pure scalar growth is too much:
=GUARD(4 mm+LOG10((Prop.Amount*1 mm)))
You will now have a circle that grows and shrinks according to the Prop.Amount value.
Similarly, you can have fun with the Color…
Actually there are two Color cells, FillForegnd and FillBkgnd, and the FillPattern value controls which one is most dominant.
If FillPattern = 1 (Solid), then only FillForegnd is shown, and Color can be expressed as RGB(n,n,n) where n is any number between 0 to 255.
Therefore you could have a formula that plays with these numbers …
For example, the following formula for FillForegnd would change the color from Red at $0 to Green at $1,000,000, going through all shades in between:
=GUARD(RGB((255-INT((Prop.Amount/1000000)*255)),INT((Prop.Amount/1000000)*255),0))
You would usually edit the Master shape to do this…
Check out this old reference, it’s still useful : http://msdn.microsoft.com/en-us/library/aa245244(v=office.10).aspx
December 30, 2011 at 3:10 pm
This is excellent. In playing around I tried to change in the ShapeSheet for a Product stencil object from Hyperlink_VisDM_Comparison>Description to the User.visEquivTitle. I saved it as a new Master stencil. No matter how many ways I’ve tried this when I link new data to the new Product object it reverts back to the description ‘Comparison.’ I’m guessing this is something to do with SETATREF behavior which is new to me. Even when i create a new master it will always revert back. Can you comment?
Thanks again.
January 31, 2012 at 11:57 am
Have you tried http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16086 ?
January 27, 2012 at 5:28 am
Hi,
I simply want to import source code into Visio Professional 2010 and create UML diagrams with it. From what I’m finding, it is either not possible, or very difficult. Is that the case, or have I missed something?
January 31, 2012 at 11:49 am
Do you get Project / Visio UML / Reverse Engineer option in Visual Studio 2010 ?
January 30, 2012 at 9:08 pm
David,
I am very very green at visio and want to use this functionality linked to an access database.
However I want to be able to automatically change connector start and end points when i change data in the data base. You had done something like this with org charts. Is there a clear detailed explanation on how to dynamically derive shapes and links for data in an access database?
January 31, 2012 at 11:52 am
Jim,
Ah … I have been asking Microsoft to provide such functionality out-of-the-box. We used to have a Flow Chart Wizard and a Network Diagram Wizard that could create connected diagrams …
I usually have an External Recordset that contains the from and to connections. I will look to see if I can generalise the code over the next few days….
January 31, 2012 at 1:49 pm
Thank you appreciate any help you can give the newbie….
March 10, 2012 at 3:44 pm
David,
If I get it right you’ve used an Excel as source for this amazing visio. Where can I find it? With the link above I get a zip with several xml but no xlsx…
I’m trying to define shapes and links outside visio and to generate a simple flowchart – like it was possible in earlier versions with csv.
Can you help me?
March 12, 2012 at 8:28 am
I have listed the link to the Excel file in the blog : http://www.visualizinginformation.com/blog/OfficeProductLinks.xlsx
Regarding automatically connecting shapes together from data: I too remember the old Network Diagram and Flow Chart Import Wizards that enabled connections to be created automatically from files. Sadly, we no longer have a similar feature, but I often have to automate this task. My approach is almost always the same, which is to use Link Data to Shapes to create linked external data recordsets. Then I write code to use this data to create connections. I have done this in my post at http://davidjpp.wordpress.com/2009/02/24/creating-an-org-chart-without-the-org-chart-wizard/ , for example.