Jason Pearce

Stephen Turbek’s 2006 article entitled “The Lazy IA’s Guide to Making Sitemaps” and Michael Angeles’ “Automating Diagrams with Visio” inspired me to adopt the strategy of first developing a website’s navigation and information architecture in Microsoft Excel and then use Microsoft Visio to provide the automatic formatting.

I love Visio and often find myself spending too much time twiddling with the look and flow of the sitemap during development instead of remaining focused on the logic, content, and organization. Turbek and Angeles’ approach to using two tools to separate the processes of organizing a website’s navigational scheme from the diagramming phase made a lot of sense to me.

The shortcoming of Turbek’s article and his downloadable Excel examples was that they limited information architects to only two levels (two clicks) deep. Several people commented that they wanted to include three or more levels deep and a few even proposed solutions.

It wasn’t until Edward Garana’s comment that I became inspired to develop a sitemap generator that can go beyond two levels. While my examples are only seven-levels deep, you could easily add as many columns as you want.

Introducing LazySitemapGenerator

Requirements

• Microsoft Excel 2003 or 2008 with Macros enabled
• Microsoft Visio 2003 or 2008 with the Organization Chart Wizard Add-On
• Download LazySitemapGenerator version 1.0

Please note that LazySiteMapGenerator uses a single Macro to do its job. You must have Macros enabled for it to work. If you want to first view the content of the Macro before opening Exel, open LazySiteMapGenerator_FindParentMacro.bas in Notepad or some other text editor.

Instructions

The Microsoft Excel files included with LazySiteMapGenerator are composed of worksheets “Input” and “Output.” As you might guess, Input is where you develop your text-based information architecture. Output is where my Excel formulas and FindParent macro defines the relationships that you’ll use to import into Microsoft Visio.

Step 1: Cells “Input!J2″ and “Output!D2″ must be the only two cells in either “M#cr#Ch#ck” (misspelled on purpose) column to have an “x” in them (empty all gray cells in both worksheets)

Step 2: The “Input” worksheet must have no empty rows/gaps

Step 3: Click on any cell in the “Output” worksheet

Step 4: Press and hold “Ctrl-A” to run the “FindParent “macro

Step 5: Repeat “Step 4″ (continue holding) until all of the values are populated in the “Output!ReportsTo” column

Step 6: Stop holding “Ctrl-A” when the Microsoft Visual Basic error pops up with “Run-time error ‘1004′,” which means you are done

Step 7: Click on “End” to clear the Run-time error 1004

Step 8: Switch back to the “Output” worksheet, click “File > Save As” and select “Text (Tab delimited)”, clicking “Ok” and “Yes” to Excel’s formatting alerts

Step 9: Close Excel, open Visio, and run the “Tools > Add-Ons > Organization Chart > Organization Chart Wizard”

Step 10: Select “Information that’s already stored in a file or database” then click “Next”

Step 11: Select “A text, Org Plus (*.txt), or Excel file” then click “Next”

Step 12: Browse to the tab-delimited file you created in “Step 8″ then click “Next”

Step 13: Select “Name” for the Name field, “ReportsTo” for the Reports To field, and leave the First Name field blank, then click “Next”

Step 14: Ensure “Name” is the only value in the Displayed Fields column, then click “Next”

Step 15: Ensure the Custom Property Fields column is blank, then click “Next”

Step 16: Select “I want to specify how much of my organization to display on each page,” check the two checkboxes, and click “Next”

Step 17: Ensure Employee at Top of Page is “Home” and Additional Levels is “All Subordinates,” then click “Finish”

Step 18: [Optional] Select “Shape > Layout Shapes” to open Visio formatting options

Step 19: [Optional] Set Style to “Compact Tree,” Direction to “Right then Down,” Depth to “Shallow,” Style to “Organization Chart,” Direction to “Left to Right,” Appearance to “Curved,” check “Apply routing style to connectors,” check “Enlarge page to fit drawing,” and click “Ok”

Step 20: [Required] Bask in your cool sitemap glory

Summary

There you go. You now have a handy process for automatically generating Visio diagrams from Excel text files. Now you can focus on the intellectual endeavor of organizing information and envisioning user work flow without being distracted by Visio’s diagramming tools.

Download Lazy Sitemap Generator

Download link: LazySitemapGenerator.zip