VBA containers and lists from external data

[aledlund]

A recent forum user brought up a request that is very common in the IT industry of wanting to diagram applications from a list in Excel. I did something like that last year (visRackAppConnectivity) to demonstrate the new Containers released in v2010. I meant to come back to it and extend it to also include Lists which was also part of the release. The users request gave me the incentive to dive back in and make a VBA version of the project.
Moving from a VB.Net implementation to a VBA version has some issues because in the VS version I made use of features that aren't in VBA. Specifically I was using tools like LINQ to create datatables for local manipulation and extended Collection methods that aren't in VBA.This brought in (among others) two issues that I would have to handle differently. The first was the working table that was a list of items that related which applications were used by which solutions. The new table/worksheet is called tblAppPeered, which is added to the previous tables: tblAttrApplication, tblAppSolution, and tblAppConnection.
The second issue is that to ease the creation of this table I created a macro in the Access database (modBuildAppPeered) that reads the tblAppConnections by way of several queries. This is an issue because a.) there is not a comparable function to auto create in Excel (that I am aware of) and b.) you can't call an Access macro via VBA. If you examine the module modAccessData in the Visio VBA project you will find a method buildAppPeered that shows how I built the Access workaround. The workaround for those that insist on Excel is to do it manually.
I also added some foundation code to help those that want to move this to a more full featured implementation (manage the Access tables from Visio) in the form of dialogs and the necessary database calls.

To operate the example:
1. Press the start button on the page. You should get a message saying the data has not been loaded
2. Select the Source tab on the form.
3. Press the Access button (the data should load)
4. Select the Solution tab
5. Select a solution
6. Press Add button....
and you're started

hth,
Al

[aledlund]

The data tables / excel workbook