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 3672 times – 103.00 Bor for Visio 2002 users:
Download “Visio Candy Equipment Sales Force Automation Demo (Visio 2002)” s!Aj0wJuswNyXlhSRHCuvpGmR93Hu4 – Downloaded 3306 times – 103.00 BThe 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
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
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.
Bill
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
Reabs
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?
Thanks
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
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
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
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
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.
Thanks!
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?
Thanks,
Jeff
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.
Thanks,
Glenn
Visio Guy says
Hi Glenn,
I’ve updated the downloads to include the Word documents. Thanks for pointing this out!
Chris
Wirelesslink says
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.