Generate Visio Network Diagrams from Excel Data?

[deanha1]

Hi All.

I'm looking for a way to try and automate the process of network planning
and I'm looking for some help/advice on using data from Excel in Visio.

To add some flesh to what I'm looking to do, there are certain elements
within the network designs that I implement that are done that are known
quantities, ie.

What kind of equipments is required for what purpose
How these devices interelate
How long it takes to configure equipment
How much the equipment costs
Naming conventions

What I would like to do is enter data into an Excel (or ideally, have these
generated through a series of prompts or drop down boxes) and then have that
data auto-generate a network diagram.

An example of the concept I'm looking to is already possible using Visio and
it's reporting function (see link)

http://msdn.microsoft.com/en-us/library/aa701255.aspx

...but I would like to is the other way around (ie. use Excel data to
generate a Visio diagram) Secondly, the resulting diagram is not very
attractive solution and I would ideally like to generate something more along
the lines of:

http://www.fr3d.org/blog/wp-content/uploads/2006/10/network-current.png

....obviously within the remit of what is realistically possible. I found one
way of doing this using Excel data and the Org Chart Wizard here:

http://office.microsoft.com/en-us/help/HA102410821033.aspx

but again, it's not a particularly pretty solution.

Does anyone have any suggestions/examples/links of something like this being
possible or in action?

I'd really appreciate the help.
Many thanks!
bdx

 

[AlEdlund]

I understand that Solarwinds has a free offering that might save you a lot
of time
http://www.solarwinds.com/products/LANsurveyorExpress/index.aspx

If you're bound and determined to do it yourself, I'd suggest you get a copy
of David Parker's book
"Visualizing Information with MS Office Visio 2007" to get a start on it.

al

 

[deanha1]

Hi There Al,

Thanks for the response. My apologies. I should have explained more clearly
in the original post. I don't need something that can analyse an existing
network setup as per LanSurveyor. It's more for planning future
implementations based on a set of known standards and variables.

For example, I know what switches, routers, firewalls we use (plus
approximate costs) for our standards and these are scaled up or down
dependant on requirement.I want to create a tool that works like a flow chart
that takes into account a few basic variables (which I assume are easiest to
work through in an Excel workbook), for example:

* How many users?
If < 26, then 1 x XYZ Switches
if > 26, then 2 x XYZ Switches

* Does it require redundant WAN connectivity?
Yes = 2 x XYZ Router;
No = 1 x XYZ router

* Does it require a firewalled environment?
If yes, firewall = yes.
If no, firewall = no

etc. etc.

As a result of these questions, what I'd like is for the tool to create a
bill of materials on these are the devices, models, quantities, approximate
pricing for them (easily done in Excel) and how long we take to configure
them and then account for this data and create a rough Visio diagram showing
basic org chart connectivity.

I know what I'm hoping to do is a bit unorthodox. It's just that I want to
try and take away the base planning of such things away from myself and put
it in the hands of a project manager who are, from what I've seen, partial
more to creating and working with spreadsheet as opposed to Visio diagrams.

Hope that makes a bit more sense. I'd appreciate any more advise you or
anyone else could offer.

Thanks again!

Dean

 

[AlEdlund]

It's not an unorthodox desire. I did something like that quite a few years
ago. The objective at that time was a 'rules based configurator', which
pushed it towards an 'expert system' solution. The language that was
ultimately chosen was Prolog. What you're describing though has two
components; product selection and connectivity. The first piece of code is
to create the product selection and connectivity data.

In theory there is nothing in your functional requirements that requires
Visio beyond possibly drawing the connectivity. Since (assumption) the
solution is probably going to be an IP based design (and the 800lb gorilla
out there rarely does meshed designs) a simple hierarchial drawing is
usually appropriate. This drives an orgchart/flowchart drawing most of the
time. David Parker just did an article on his site touching on the
techniques for this type of drawing
http://bvisual.spaces.live.com/blog/cns!3350D61BC93733A9!1311.entry

The BOM portion already has examples out there.
http://msdn.microsoft.com/en-us/library/aa140253(office.10).aspx

al

 

[deanha1]

Hey Again Al,

Very fair point about going more "simple hierarchical" diagram over pretty.
My aim here apart from cosmetic was to try and keep it in line with all
existing network diagrams in Visio (using the standard Cisco icon set) I
guess I have to decide the lesser of two evils and the links you sent along
are pretty much in line with what I wanted to do so thank you VERY much.

One last question. From what you know, is it possible to define specific
icons instead of boxes in the Visio Org Chart wizard? I think this would
solve my last mental problem.

Thanks again for your help Al! Much appreciated.

Dean

 

[AlEdlund]

The material that David was using should be modifiableto use any visio
shape. Here's another example of playing with layout and routing
http://visguy.com/vgforum/index.php?topic=585.0

Regarding "try and keep it in line with all existing network diagrams in
Visio"
http://office.microsoft.com/en-us/templates/results.aspx?qu=visio+network+templates&av=TPL000

al