Auto draw Visio drawing by importing Excel data

I was ask to make a overview to see which servers are responsible for which application. This overview is for management and must be in a drawing format and they want to have this available when they want to. This is task that can cost a lot of time just for a quick overview. So how can I make it without buying an application and with the least amount of effort? So a went looking around and found out that you can import Excel data in Visio and that Visio can make a drawing of it if you choice the right options.

In most IT environment they will keep track of servers and possible there function in some kind of format. In my case i had two options make a extraction of the vCenter data or export the SharePoint data in to Excel. The first way is pretty strait forward select the view and make a extraction by making a export. In this blog I will go and do it the ”hard” way because of the extra steps you will have to make. Some screenshots are with Dutch sorry for that!

 

Go to share point and select the data you will like to extract to excel. Make sure that All Items are selected. The All Items is on the right site in a “default” site.

 

 

 

 

Now on the left side of the site click Actions and click Export to Spreadsheet

 

 

 

 

Click Open

 

 

 

 

 

You may get this error because of the Query’s that are in the file. click Open

 

 

The raw data is now available in Excel and must be transformed in to usable information to have a good import in Visio.First thing that must be done is to remove all hyperlinks that are in the Excel sheet.

Press Alt+F11 and you will be in the Visual Basic editor  go to  Insert  en choose Module

 

 

 

Add the the following subroutine:

 

 

 

Sub RemoveHyperlinks()

‘Remove all hyperlinks from the active sheet
Active Sheet.Hyperlinks.Delete

End Sub

Go to File and close the Visual Basic editor and return to Excel

 

 

 

 

In Excel go to Extra  and click Macro, ore use Alt+F8, to run the macro you just made.

choose the Module RemoveHyperlinks  and run it. The Excel sheet is now free of hyperlinks.

 

 

 

 

Now it is time to remove the data range which is in the sheet. go to Data and select data range  properties.

 

 

 

 

Remove the checkmark from Save Query.

Click OK.

You will get an popup which will tell you that this action can not be undone.

 

 

 

 

T
he excel sheet is ready for importing to give you an idea the picture below is an example of how you sheet could look like. You can have as many columns as you like. the important thing is that we would like to use relations in Visio and there for all application names (in this case column B) must all so be in in column A.  This is necessary to make the relations. the data in row B 34 and below are queries I use to make a SUM of the numbers above this is not necessary. 

Close your Excel sheet and open Visio. Went the sheet is open and you will try to import it in to Visio it will give you an error.

Go to File  >  Organisation Chart > Organisation Char Wizard

· Select Information that’s already stored…

Click Next

 

 

 

 

· Select A text, Org Plus…

Click Next

 

 

 

 

· Browse to the excel file

Click Next

 

 

 

 

· Select the right columns.

By Name select the servers you would like to see in the drawings By report to select the applications. This way you will get a drawing per application and will see which server is being use for the application

Click Next

Select the data you want to see in the drawing view

Click Next

 

 

 

Select the data you want to see in the property field of a shape.

 

Click Next

 

 

Select I want the wizard to automatically….

Click Finish

 

 

 

 

When Visio is ready with the import. In my Excel sheet I have 6 differenced applications so I  have got 6 TAB in Visio for each Application a differed one. In the top Is the application, the square shape with the double line, and the SUM of usage server, CPU, memory count this is the data from column B34 I mentioned. the square shape below are the servers with there selected data to see.

when you right click one off the square shapes you are able to select the property of the shape like this view.

 

 

 

 

 

 

  Some times (I don’t know why) the shape are not ordered at nice way. Just select all and go to Organiram 

 

 

And choose the order you would like to see the shape.

 

 

 

When importing in Excel, Excel will use the default shapes this can be adjust in front or afterwards use you friend Google to figure this out : ).

This has bean tested on Excel 2
007 and 2010 and Visio 2007 and 2010 some time the function is in a other place but you will figure it out 😉 The screenshots are from excel and Visio 2003.