Pearls from the auld Scotsman
Few people know Visio software and communicate their knowledge as well as Dave Edson. In this regular column Dave dispenses pearls of wisdom in his own inimitable style.

Custom Properties in Visio: Brilliant!

Part II

David A Edson

Adding custom properties to your shapes in Visio is the ultimate in intelligent technical drawings. Dave shows how to use this powerful facility in Visio in Part I and how to exchange data between custom properties and Excel in Part II.

You can add Custom Properties to ANY Visio SmartShape and store whatever information you wish in them. To show the power of accessing this information, we will next embark on the process of creating a small VBA application to extract the Height, Width and Text information out of the Custom Property cells and pass them off to an Excel spreadsheet. As ye might o’ guessed, you will need Excel to be present on your wee computer for you to test all of this out.

All right then… The first thing that you need to do to is learn how to jump into Visio’s VBA development environment. The true programmer’s (and Microsoft’s preferred) shortcut is pressing the Alt+F11 key combination. Of course you could have also selected Tools > Macro > Visual Basic Editor, but you already knew that. This launches the VBA IDE. That’s because MSFT, Microsoft, loves TLA’s, Three Letter Acronyms. VBA IDE is the Visual Basic for Applications Integrated Development Environment. If you haven’t poked around here much don’t let it intimidate you. We will walk through this step by step. First look at the Project Explorer Window. If you don’t see it select View > Project Explorer. In the project window you will see your current Visio drawing as the listed project. Under that you will see Visio Objects, and under that you will see This Document. We are going to create a brand new module to work with in this project. Select the Project name, and then select Insert > Module. This will create a new Modules entry under the project and a new Module1 under the Modules. If the code window for Module1 is not showing, double-click on the Module1 item in the explorer and it will appear.

Grand! Now we need to make a new Subroutine to export all of the custom property information in our current drawing to an Excel Spreadsheet. It might be a great idea about this time to include a reference to Excel in our project so that we can take advantage of the Excel Object Model. To do this, select Tools > References… and bring up the References Dialogue. In the dialogue, find and check the check box for, from the list presented, "Microsoft Excel 8.0 Object Library", then click the OK Button. You have just added the Excel library to this project. Next let’s put together the framework for our new Subroutine. Click into the Code Window and type in the following line:

Public Sub Pass_To_Excel ()

Now press the enter key. Note that VBA completes the task for you by adding the line "End Sub" below it. This is the subroutine framework and the function that we will call to export our Custom Property data to Excel.

The listing that follows is the complete code including the framework. You will need to enter all of the information within the framework without duplicating the first and last lines.

Public Sub Pass_To_Excel()
Dim appExcel As Excel.Application'The Excel application object
Dim xlBook As Excel.Workbook'The Excel workbook object
Dim xlSheet As Excel.Worksheet'The Excel spreadsheet object
Dim selObj As Visio.Selection'Shapes selection collection
Dim shpObj As Visio.Shape'A shape instance
Dim i As Integer
Dim row As Integer

'Set shpsObj to the current selection
Set selObj = Visio.ActiveWindow.Selection
Set appExcel = CreateObject("Excel.Application")

'Note: unlike Visio, Excel is not visible by default 
'when you create a new instance.
'The next statements makes Excel visible, create a new 
'workbook and access the first worksheet.
appExcel.Application.Visible = True
Set xlBook = appExcel.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet1")

'Note: row keeps track of which row we are writing into in the Excel spreadsheet.
row = 1

'Note: on the next line, Cells is an Excel object method.
xlSheet.Cells(row, 2).Value = "Width"
xlSheet.Cells(row, 2).HorizontalAlignment = xlCenter
xlSheet.Cells(row, 3).Value = "Height"
xlSheet.Cells(row, 3).HorizontalAlignment = xlCenter
xlSheet.Cells(row, 4).Value = "Text"
xlSheet.Cells(row, 4).HorizontalAlignment = xlCenter
row = row + 1
For i = 1 To selObj.Count

'Set shpObj to the ith shape in the selection
Set shpObj = selObj(i)

'xlSheet.Cells(row,1).Value = the name of the shpObj
xlSheet.Cells(row, 1).Value = shpObj.Name

'xlSheet.Cells(row,2).Value = the value of the Cost custom property in shpObj
xlSheet.Cells(row, 2).Value = shpObj.Cells("Prop.Width").Result(visInches)

'xlSheet.Cells(row,3).Value = the value of the Cost custom property in shpObj
xlSheet.Cells(row, 3).Value = shpObj.Cells("Prop.Height").Result(visInches)

'xlSheet.Cells(row,4).Value = the value of the Cost custom property in shpObj
xlSheet.Cells(row, 4).Value = shpObj.Cells("Prop.Text").Formula
row = row + 1
Next i
MsgBox "Visio Drawing Data Successfully Exported to Microsoft Excel"
End Sub

All right, all right, all right… I know that may seem a wee bit much to digest at one time. A wee bit like yer first taste o’ haggis. But let’s break it down and discuss each piece.

First the declarations section:

Dim appExcel As Excel.Application'The Excel application object
Dim xlBook As Excel.Workbook 'The Excel workbook object
Dim xlSheet As Excel.Worksheet'The Excel spreadsheet object
Dim selObj As Visio.Selection'Shapes selection collection
Dim shpObj As Visio.Shape'A shape instance
Dim i As Integer
Dim row As Integer

This is where we use the VBA keyword DIM, which means to Dimension, or declare. We declare a variable name, this could be any name, say Foo or Bar, and we state its data type. This lets VBA know what we intend to do with the data to be stored in that variable.

Next we are going to let VBA find all of the selected items in the Visio drawing

'Set shpsObj to the current selection
Set selObj = Visio.ActiveWindow.Selection

Then we will let VBA open Excel and allow us to see Excel running.

Set appExcel = CreateObject("Excel.Application")

'Note: unlike Visio, Excel is not visible by default
'when you create a new instance.
'The next statements makes Excel visible, create a new workbook and
'access the first worksheet.
appExcel.Application.Visible = True
Set xlBook = appExcel.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet1")

With that done we will next place some nice wee titles on the first line of the Excel spreadsheet.

'Note: row keeps track of which row we are writing into in the Excel spreadsheet.
row = 1

'Note: on the next line, Cells is an Excel object method.
xlSheet.Cells(row, 2).Value = "Width"
xlSheet.Cells(row, 2).HorizontalAlignment = xlCenter
xlSheet.Cells(row, 3).Value = "Height"
xlSheet.Cells(row, 3).HorizontalAlignment = xlCenter
xlSheet.Cells(row, 4).Value = "Text"
xlSheet.Cells(row, 4).HorizontalAlignment = xlCenter
row = row + 1

Following that, we will then proceed to look at each selected shape, gather up the Custom Properties and hand them off to the designated Excel spreadsheet cell. This is done in a simple FOR – NEXT loop.

For i = 1 To selObj.Count

'Set shpObj to the ith shape in the selection
Set shpObj = selObj(i)

'xlSheet.Cells(row,1).Value = the name of the shpObj
xlSheet.Cells(row, 1).Value = shpObj.Name

'xlSheet.Cells(row,2).Value = the value of the Cost custom property in shpObj
xlSheet.Cells(row, 2).Value = shpObj.Cells("Prop.Width").Result(visInches)

'xlSheet.Cells(row,3).Value = the value of the Cost custom property in shpObj
xlSheet.Cells(row, 3).Value = shpObj.Cells("Prop.Height").Result(visInches)

'xlSheet.Cells(row,4).Value = the value of the Cost custom property in shpObj
xlSheet.Cells(row, 4).Value = shpObj.Cells("Prop.Text").Formula
row = row + 1

Next i

Finally we display a message box to let the user know we have completed the task.

MsgBox "Visio Drawing Data Successfully Exported to Microsoft Excel"

And That is it!!! Ye see? Not so bad after all!!

This simple example can, of course be greatly expanded upon to make very robust solutions to reporting on exactly what you need to retrieve from a Visio drawing and interact with an external application.

Now… to run this application, close the VBA IDE, and return to the Visio drawing. Ensure that you have several instances of your new rectangle shape out on the page and that you have filled in all of the Custom Properties. Now with all of the shapes selected, select Tools > Macro > Module1 > Pass_To_Excel and watch the automation take effect.

So lads an’ lassies we have seen how to add Custom Properties to any Visio SmartShape, how to fill in the Custom Properties, and how to create and execute a VBA program that can look at the particular Custom Properties you are interested in and hand them off to an Excel Spreadsheet.

I hope you will try this all out and give yourself that "leg up" to making yourself even more productive in your use of Visio as the single standard for creating, storing and exchanging valuable business drawings.

"Haste ye back."

Dave "The Auld Scotsman" Edson