Home » Code, Shapes

Sales Force Automation With Visio

Submitted by on June 3, 2007 – 6:30 am | | 65573 views 20 Comments

« »

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:

[download id=”73″ format=”2″]

or for Visio 2002 users:

[download id=”72″ format=”2″]

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:

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!

Cand-e-quip Finished System

click to view larger image


  • Bill says:

    I’m not the brightest bulb in the pack and you seem to know Visio so I hope you don’t mind me posing a question to you. I am an automation engineer programming PLC’s. I can place live data from a PLC on to an excel speadsheet. What I want to do is cause shapes to change color based on the values in my excel spreadsheet. Can you point me in the right direction? I have posted the same question on several forums, but I haven’t had any responses.

    Thanks you for your time.


  • reabs says:

    Right, just the excuse that I needed. I also need to ask a related question that has been driving me mad for ages. Well sort of related. I am still stuck with 2003, so please bear that in mind.

    My problem is in mapping dependencies. I figure that if you have a 1d arrow connecting two 2d shapes, there must be some way of exporting to access or excel such that you can say block A precedes block B.

    The frustrating thing is that the report function pushes:
    -The master name
    -The shapeID
    to excel.

    This helps in automatically identifying the blocks that are dependant on one another.

    On the other hand the database export function shows which ShapeID’s the 1d endpoint are glued to.
    This is in a string, but with a bit of code this can be separated out to produce the dependency.

    This helps is automatically showing the dependency between the two blocks.

    With both pieces of information, the complete picture can be constructed.

    How can we customise the export function in Visio, to choose which fields we want to export? (So how can I specify the master and shapeID fields to be included in the export?)

    (Or is there any other way of doing this)

    Any help would be awesome, thanks


  • Barb says:

    Chris – I just had to let you know that I was able to leverage this example in a recent support case! Wonderful sample, thanks again 🙂

  • Visio Guy says:

    Hi Barb!

    The Candy Factory Demo refused to die. And everybody laughed when I first created it…

    – Chris

  • Rachael says:

    You mentioned in your intro that demo was originally created for Visio before 2003. I am running 2002 and the some of the shape features don’t work with this file.

    Do you happen to have an older version?


  • Visio Guy says:

    Hi Rachael,

    Well, I thought that I had uploaded a 2002-compatible version, but evidently it is 2003/2007. I’ve just added a link for a 2002 version, also way up at the top of the article. Hopefully this will get you going!

    – Chris

  • Nicolas says:


    I was hoping i coulg get some help. Iam currently designing a reactor, but my calculations in excel keep changing the final size of the reactor. Is it possible to link the cell in excel to the size of the shape in visio? For instance if the calculations come to a height of 7cm, can viso automatically take this number and adjust the reactor height accordingly

    Any help is much appreciated

  • Lars-Erik says:

    Nicolas, you might be interested in this topic on the forum:
    shape dimensions driven by linked data
    Its the most popular post at the moment, and I think i should explain all your questions.

    – Lars

  • Lars-Erik says:

    “it” should explain all you questions.
    Also, for the excel linking part check out:
    this article

  • Steve Sisk says:

    Link to MSDN location is dead.

  • Visio Guy says:

    Thanks Steve. I think it’s been dead for awhile, that’s why I’ve provided the .zip files here in this article.

  • meerubus says:

    Is it possible to generate C# codes from Visio flowchart?
    For example, if i draw a flowchart of a system including decision boxes, looping, sequence control etc.. Then can Visio churn out the corresponding C# codes? ie. like a program generator.


  • Visio Guy says:

    Hi meerubus,

    Have a look at this article:

    Create Visio Flowcharts Programmatically

    It’s kind of the reverse of what you need to do, but it shows you the power of Visio automation.

    The Visio SDK contains examples for tracing connections in Visio diagrams, ie: which line is connected to which shape. With this info, you could build the tool you are envisioning!

    – Chris

  • Sulsa says:

    The zip file only contains the Visio file. When I insert a shape it asks for the access db. Can you please repost the example with all of the files? Your website has been great in helping me understand how to make a configurator for my company.


  • Visio Guy says:

    Good catch, Sulsa!

    Check the links again, I’ve uploaded new .zip files that include the database.

    Thanks for finding this,

    – Chris

  • Jeff says:

    Hey Chris, Thanks for this example. It pointed us in the right direction in creating a BOM in a similar manner and we have been using it for a number of years now. However I have recently upgraded to Visio 2010 Beta and the macro no longer works. It fails here:
    Set xlSheet = ThisDocument.Pages.Item(“TitlePage”).Shapes(xlName).Object.Worksheets(1)

    The Object property apparently no longer contains Worksheets. Any clues how to fix this?


  • Visio Guy says:

    Hi Jeff,

    Just shooting from the hip, I am wondering if there is a missing reference to Excel in the VBA project?

  • glenn.pringle@gmail.com says:

    Hi Chris,

    Do you still have the Word document that should be included with the demo (notes on the demo, as well as the Demo Script for walking you a test-scenario)?

    I just downloaded both files but don’t see any reference to it.



  • Visio Guy says:

    Hi Glenn,

    I’ve updated the downloads to include the Word documents. Thanks for pointing this out!


Leave a comment!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code lang=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre lang="" extra="">

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.