Author Topic: Batch Creation of Network Diagrams from Excel  (Read 8255 times)

0 Members and 1 Guest are viewing this topic.

platipus_

  • Newbie
  • *
  • Posts: 3
Batch Creation of Network Diagrams from Excel
« on: April 08, 2011, 01:50:56 PM »
I am on a network transition project. It consists of about 1500 remote sites each of which have a very small network that needs to have a network and rack diagram created along with a few other bits of info, all of which we have in Excel for tracking purposes.

Of the 1500 sites, there are about 10 different types (minor variations) with templates that never change except for the site specific info (hostnames, IP's, BGP ASN, site address, site ID's, telephone # for modem, things like that)

Each network drawing template consist of 4 tabs.

1) A generic rack layout that just needs a hostname labled to the device in the rack. Positions never change. Literally just need hostnames applied to each device at the right side of the rack parallel with the device.

2) An equipment list page that just needs a hostname labled at the top of each grouping of equipment. Equipment never changes in each "type" of template.

3) A very simple network diagram page that consists of 2 - 6 connected generic "square boxes" that represent devices (routers, switches, modems) that would need a hostname, IP, Interface and Part# label associated, all of which is available in Excel.

4) A background page that has things like address info and some boilerplate legal/privacy advisories.


From what I have seen/read, it's easy enough to set up the site specific data to auto populate the shapes in a template with a row of Excel data.

Here is the question I have:

Is there a way to automate the creation of the 1500 network diagrams using that data? What I am am hoping to acomplish to to run a script or something that will import a row of excel data into the drawing to populate all of the site specific info, save that file out as an individual Visio named for the site ID of the site and then move on to the next row of Excel data and do the same. I am hoping this can be done without having to manually intervene in each seperate file.

Does anyone have any ideas or suggestions? Can this be done without a huge amount of scripting?

Thank you!!

aledlund

  • Hero Member
  • *****
  • Posts: 1412
Re: Batch Creation of Network Diagrams from Excel
« Reply #1 on: April 09, 2011, 06:55:06 AM »
"can it be done?", yes. It sounds like a lot of the prework has already been accomplished (template creation, necessary data capture, decision to use generic shapes, etc.). There are example projects on the site on how to read data from Excel and assign data to shapes. There are examples on opening template drawings in the Visio SDK and saving the files. What has to be added is the necessary logic to identify which template to select based on the datarow and the custom code to apply the information correctly to each template. I suspect "scripting" is overly optimistic, but a VBA project is very doable.
Al

platipus_

  • Newbie
  • *
  • Posts: 3
Re: Batch Creation of Network Diagrams from Excel
« Reply #2 on: April 11, 2011, 09:17:34 AM »
That is good news to start. As far as scripting the decision for which template to use, I am OK without that for now. Since there only about 10 different templates, just getting a single scripted run of one template type would be a big time saver. I could just set up 10 different data seed files in Excel and then point to a different template file for each one. Getting it to select the template type could go into v2.0 of this. What's really important here is that the automation runs the whole list of file creations with no manual intervention needed once it kicks off. 

I will dig around for those examples you mentioned.

As far as the creation tools needed, I have Excel, I have an approval to upgrade from Visio 2007 Standard to 2007 Pro (2010 is not part of any standard software packages supported by my company yet, so I have to wait) and I can download the Visio SDK from Microsoft. Is there anything else I would need? Assume I have nothing else. No other scripting/coding tools installed currently.

Once I identify the all the needed tools/pieces, I can go back to my boss and ask for some scripting help from someone else in our company to fill in where I am weak.

Thanks!

aledlund

  • Hero Member
  • *****
  • Posts: 1412
Re: Batch Creation of Network Diagrams from Excel
« Reply #3 on: April 11, 2011, 10:04:18 AM »
the v2010 sdk can be used for most of what you want to do. The good news is that the v2010 version of the sdk supports three languages, one of which is vba.
Al

platipus_

  • Newbie
  • *
  • Posts: 3
Re: Batch Creation of Network Diagrams from Excel
« Reply #4 on: April 12, 2011, 09:29:37 AM »
How about the v2007 SDK? That is my easiest upgrade path right now. I am in a tightly locked down environment and getting v2010 would require some hoop jumping.

Thanks!!

aledlund

  • Hero Member
  • *****
  • Posts: 1412
Re: Batch Creation of Network Diagrams from Excel
« Reply #5 on: April 12, 2011, 11:52:20 AM »
Neither of the SDKs has a dependency on the Visio product being installed, I recommended the v2010 one because of the VBA content.
al