Excel to Visio Site Map Generation

I've been using Visio 7’s Organization Chart Wizard to import data from Excel and create a multi-level site map, with many thanks to Stephen Turbek, Edward Garana (comment on Stephen's post) and Jason Pearce.

 

The process described below does not require an Excel macro, and uses an extended outline numbering style to accommodate multiple levels, with a simple formula in the Reports To column to calculate the site map element parent.

 

Here are the steps to create the Visio site map from an Sample Excel File: Example_School.xlsx, with limitations indicated below:

 

1. Create Excel file according to structure provided in the attached template. Note the following:

• Name field must be unique
• Use outline numbering style to support multiple levels
• Note trailing period (used to support LEN function in Reports To column)
• Title field is the label for the item in the site map
• Reports To references the parent, using the id in Name column
• The attached file is incomplete and serves as an example only

2. Import the data in Visio 7. Note the following:

• You must close the file in Excel before importing into Visio, or the import will fail
• In Visio, go to File>New>Business>Organization Chart Wizard
• Select: Information that’s already stored in a file or database:
  
  
• Select: A text, Org Plus (*.txt), or Excel file:
  
  
• Browse to your file location; make sure the file is closed in Excel:
  
  
• Map columns to fields as follows:
  
  
• Set display options as follows:
  
  
• Leave shape fields blank:
  
  
• Decide if you want to define page layout, or let Visio do it:
  
  
• In this example, I want to specify, and I opt to include all shapes on one page:
  
  
• Click finish to complete import wizard. Visio should render a multi-level site map based on the Excel values.

Promising results, however there are a few limitations:

1. The Organization Chart Wizard uses the default Organization Chart shapes. To get this to work with your organization's site map template shapes, we will need to override this default.
2. The import wizard does not import the shapes in order, but rather randomly. As such, rearrangement is required if the numbering structure must be maintained.
3. The calculation used in the Reports To: column is only valid for 9 levels.

I'm posting here because I would be happy to hear from anyone in the community has any thoughts on how to address the limitations or extend this process.