• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Visio Guy

Smart graphics for visual people




  • Home
  • Hire Me
    • Hire Me
    • Résumé
  • Products
    • Products
    • Bubble Revision Shape
    • Layers to Pages Utility
    • Rack Unit Dimension Line
    • Radial Elements Tool with up to 100 Wedges
    • Text on a Circle Visio SmartShape
  • Index
    • Articles by Date
    • YouTube – VisioGuy
    • Download Information
    • Suggestion Box
    • Shop
    • Visio Art
    • Visio Links
    • – Visio Shapes & Stencils
    • – Visio Templates & Drawings
  • About
    • About
    • Donate
    • GitHub
    • jsFiddle
    • Reading List
    • Subscribe & Follow
      • – E-mail
      • – facebook
      • – Twitter
      • – RSS
    • Privacy Policy
  • Discussion Forum
You are here: Home / Development / Code / Sales Force Automation With Visio

Sales Force Automation With Visio

June 3, 2007 By Visio Guy 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 “Visio Candy Equipment Sales Force Automation Demo”

s!Aj0wJuswNyXlhSdDLy_XXG0e89R9 – Downloaded 1664 times – 103.00 B

or for Visio 2002 users:

Download “Visio Candy Equipment Sales Force Automation Demo (Visio 2002)”

s!Aj0wJuswNyXlhSRHCuvpGmR93Hu4 – Downloaded 1332 times – 103.00 B

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!

Cand-e-quip Finished System

click to view larger image

  • Tweet
  • More
  • Pocket
  • Share on Tumblr
  • Print
  • Email

Related posts:

  1. Visio Snowflake Generator
  2. Polygon Maker
  3. Circular Text Generator (version 1)
  4. European Cup 2008 Auto-Updating Visio Diagram
  5. European Cup 2008 Auto-Updating Visio Diagram (v2)

Filed Under: Code, Shapes Tagged With: BOM, Code, Database, Excel, Integration, Programming, Sales Force Automation

Previous Post: « Run VBA Code When Documents Open
Next Post: Multi-Earth Shape »

Reader Interactions

Comments

  1. Bill says

    July 13, 2007 at 10:11 pm

    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.

    Bill

  2. reabs says

    August 30, 2007 at 3:15 pm

    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

    Reabs

  3. Barb says

    March 7, 2008 at 9:54 pm

    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 🙂

  4. Visio Guy says

    March 8, 2008 at 7:17 am

    Hi Barb!

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

    – Chris

  5. Rachael says

    May 21, 2008 at 10:41 pm

    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?

    Thanks

  6. Visio Guy says

    May 22, 2008 at 4:10 pm

    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

  7. Nicolas says

    June 10, 2008 at 2:42 pm

    Hi!

    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
    Thanks

  8. Lars-Erik says

    June 10, 2008 at 3:27 pm

    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

  9. Lars-Erik says

    June 10, 2008 at 3:31 pm

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

  10. Steve Sisk says

    December 3, 2008 at 7:14 pm

    Link to MSDN location is dead.

  11. Visio Guy says

    December 4, 2008 at 6:48 am

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

  12. meerubus says

    February 2, 2009 at 11:47 am

    Hi,
    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.

    Thanks.
    Mee

  13. Visio Guy says

    February 16, 2009 at 9:54 am

    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

  14. Sulsa says

    February 27, 2009 at 3:07 pm

    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.

    Thanks!

  15. Visio Guy says

    February 28, 2009 at 1:53 am

    Good catch, Sulsa!

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

    Thanks for finding this,

    – Chris

  16. Jeff says

    December 24, 2009 at 5:56 pm

    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?

    Thanks,
    Jeff

  17. Visio Guy says

    December 30, 2009 at 11:16 am

    Hi Jeff,

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

  18. glenn.pringle@gmail.com says

    July 26, 2011 at 5:07 pm

    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.

    Thanks,

    Glenn

  19. Visio Guy says

    July 27, 2011 at 5:27 am

    Hi Glenn,

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

    Chris

  20. Wirelesslink says

    January 19, 2016 at 1:01 am

    Do you have an updated version of this using Visio 2013/Access 2013 ? Looks like a very cool concept ! Would like to learn more details on it especially if it can work with the newer office versions.

Leave a Reply Cancel reply

Primary Sidebar

Buy Über Bubble Revision Shape
Purchase

Categories

Buy my book!

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Tag Cloud

A/V Artistic Effects BPM Code Connectors Control Handles Countries Custom Patterns Custom Properties Data Graphics Data Linking Data Visualization David Edson David Parker Fill Format Formulas Functions Geometry Gradient Images Links Maps Multi-shapes Network Programming repeating shapes Resources Right-Click Actions Scale Shape Data ShapeSheet ShapeSheet Formulas ShapeSheet Functions SharePoint shiny SmartShapes Sport Sports Text Themes Tools Transparency User-defined Cells Visio 2007 Visio SmartShapes

Top Posts & Pages

  • - Visio Shapes & Stencils
  • - Visio Templates & Drawings
  • Amazon AWS Visio Shapes
  • Sankey Diagram Shapes for Visio
  • Free Visio People Shapes
  • Bubble Revision Shapes
  • AV Engineering Diagrams with Symbol Logic ECAV
  • Release the Power of Visio Custom Line Patterns
  • Crayon Visio Network Shapes, Revisited
  • Text Along a Connector's Path in Microsoft Visio 2010

www.visguy.com - Visio Guy - since 2006

 

Loading Comments...