Sales Force Automation With Visio

One of the areas in which Visio particularly shines is that of Sales Force Automation. With Visio, a rep can sit on-site with a customer, and visually spec out a system or an installation. The components in the Visio schematic can be linked to real product-data, and can be analyzed via code to generate bills-of-material and reports automatically.

On MSDN, I recently uncovered “oldie but goody” demo that shows off these capabilities. I created it for Visio 5.0, while I still worked for Visio Corp, and it still works with Visio 2003! The demo illustrates how a fictitious candy-manufacturing line might be laid out. Custom shapes represent general classes of equipment, and database links behind the shapes specify real-world items. With the click of a button, a Bill of Materials, is generated in an embedded Excel spreadsheet, sitting right on the page!

If you’re interested in Visio as a sales-force automation tool, or just want to rip apart SmartShapes and VBA code to see how to build Visio-based solutions, then I urge you to have a look at this solution. You’ll find it on MSDN here: Custom Visio Solution: Candy Equipment Manufacturing Example.

Edit: The Microsoft link doesn’t seem to be working anymore. You can download the Visio file here:

or for Visio 2002 users:

The demo comes complete with a Visio Template, an MS-Access database of all the candy equipment (stop lauging!), and a Word document that includes notes on the demo, as well as the Demo Script for walking you a test-scenario.

Elements of Technology

As detailed on the MSDN site, the elements of the technology that you’ll find in this demo include:

• Custom shape design: The candy manufacturing equipment shapes are custom-built for this demo. They illustrate how Visio shapes can have high-quality graphics, contain parametric behavior, link to databases, store data as custom properties, and invoke automation code. The ShapeSheet interface is a spreadsheet-like tool that comes with Visio technology. It defines behaviors and data properties that are part of every shape. As in a traditional spreadsheet, ShapeSheet cells can contain conditional formulas and references to other cells, which can be built up to give shapes their smart behavior.
• Parameterized shapes, ShapeSheet interface, smart graphics: The SmartShapes symbols react visually to data. When a shape is dropped, the user specifies the exact type of equipment the shape represents by choosing a manufacturing line and model number from the database. These properties serve as keys to other data about the equipment. For example, the size and appearance of the shape changes according to this data. The ShapeSheet interface is used to program this parametric behavior.
• ActiveX automation code Visual Basic: Visio products include the Visual Basic for Applications (“VBA”) development environment. This enables developers to place automation code behind a drawing. In this demo, VBA code is used to create the bill of materials, to make sure the ODBC database is registered when the drawing is opened, and to place shape data in the text of the Auto-label callout shape when it is dropped over equipment. You can also use other automation-capable development environments such as Visual Basic and Microsoft Visual C++ to control Visio operations.
• Database Wizard: The Database Wizard is a Visio add-on that uses ODBC to connect the shapes to the Access database. The add-on is written in C++ and uses ActiveX automation to manipulate the Visio Object Model. (The add-on is part of the Visio product, but is written separately from the core engine. Third-party developers can create their own database wizards. The add-on is written as a VSL, which is a special Visio DLL file, and is stored in the Visio\Solutions directory, so that all shapes can access its functions.)
• Microsoft Office integration: The embedded Excel object shows how well Visio works with standard software components. The embedded spreadsheet receives information from the drawing to create a bill of materials. This illustrates how solutions can be built from standard components. Rather than try to build a grid out of Visio shapes, why not use the best tool for the job? Hence the embedded Excel spreadsheet.
• ActiveX controls: The ActiveX command buttons perform tasks such as creating the bill of materials and clearing the embedded spreadsheet object. These buttons invoke code stored in the document’s VBA project.
• Custom patterns: The Conveyor shape can be configured to show wrapped and unwrapped candy. This shape can be stretched to any length and still have an appropriate appearance. Visio custom pattern masters are used to represent the candy, as well as the linkages on the edges of the Conveyor shape.
• SmartShapes symbols that call code: The Auto-label callout shape uses the CALLTHIS ShapeSheet function to access a VBA procedure whenever its endpoint moves. This code determines which shape the callout is pointing to and reports the shape’s custom-property data in the callout’s text. The equipment shapes use the RUNADDON ShapeSheet function to access the Database Wizard’s functions when the user drops the shapes or chooses certain right-click menu items.
• Layers: If a Conveyor shape is stretched past a certain width, a special bitmap appears. What happens behind the scenes is this: When the Conveyor shape is stretched beyond a certain point, a SETF formula toggles a value in the page’s ShapeSheet. A ShapeSheet cell that governs the visibility of the bitmap layer has a formula that refers to the toggle value, and the bitmap magically appears! With Visio layers, you can classify shapes on a page, hide them, lock them, and so forth. Because these layers are governed by ShapeSheet cells, conditional behavior can be placed upon layer properties themselves.
• Automated setup: This demo involves multiple files and some system settings (ODBC data source creation), but the VBA code takes care of it all. Just copy the directory and you’re ready to go!

VBA Code & Changes to your System Settings

Note: the document contains VBA code that runs when you open the document. For more information on VBA and Security, see the post: VBA Macro Security. When the document is opened, and ODBC data source will be created using the following code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Public Sub SetODBCDatasource( _       sDatasourceName As String, _       sDBName As String, _       sDriverName As String)     '// ...comments...     Call DBEngine.RegisterDatabase( _         sDatasourceName, _         sDriverName, _         True, _         "DBQ=" & sDBName)   End Sub

Public Sub SetODBCDatasource( _
      sDatasourceName As String, _
      sDBName As String, _
      sDriverName As String)

  '// ...comments...

  Call DBEngine.RegisterDatabase( _
        sDatasourceName, _
        sDriverName, _
        True, _
        "DBQ=" & sDBName)

End Sub

Nothing show-stopping here, you’ll just end up with another data-source on your machine that points to the local Access database. But I think it’s the right thing to do to inform users as to what is happening to their systems.

A Finished Candy-manufacturing System

Since the demo includes a barely-started system, I thought I’d show you what a completed candy manufacturing line looks like (stop laughing again!) You can see how more equipment has been added to the line. The wrapper, the paper feeder, the conveyor belts, etc. Note also that the Excel BOM has been filled out, and that the user is changing the right-most conveyor belt to depict wrapped candy. This feature utilized custom patterns to generate the repeated graphical elements!

click to view larger image