Data Visualizer (DV) in Visio Plan 2 (Data | Create from Data | Create ) is great because it provides a way of automatically creating a diagram from data, but it also prevents some of the other data-linking features in Visio from being used. This is because DV wants to take control of the data in the shapes to provide refreshes from the data source or send updates to the data source. This can be very annoying if you use DV to create the initial layout, then you enhance this, so it looks just perfect, and then you just want to update some of the data without affecting the layout. DV may re-layout your carefully crafted diagram, even if there are no updates to the data that should do that. Also, you may want to integrate data from other sources that were not available or accessible to the table or query used for by Data Visualizer. This is when you may want to use Data | External Data | Custom Import which also provides a much greater number of data source types. In this article, you will learn how you can do this manually, or automate it with the help of a little VBA code (or similar).
In this example, I have used table of personnel that I have used before, but added an extra column, Shape Type, that simply has the value Rectangle, which is simply the name of a simple Master shape that I have added to my macro-enabled stencil, ExDataLinker.vssm.
Note: There are two Master shapes that Visio will create automatically, ven if they do not exist in the active stencil. These are Dynamic connector, because it is need by many connection tasks, and the second is Rectangle, which is needed whenever a row from an External Data window is dragged on to a page, and no other suitable Master is selected.
Then I added another Excel table/query, Personnel Stats$ that contains some statistical information about each person.
Note: DV prefers to use a modern Excel table/query, but Custom Import uses a worksheet, so it is best to have these tables starting in cell A1.
I could drag each row from the External Data window to the matching Person shape that is already on the page, but this is quite tedious. I would normally use Link Data.. from the right mouse menu of the active External Data window, and then choose to automatically link each row with the value in the selected Data Column to a selected Shape Field, but the presence of the DV container prevents this option from being available.
So, I needed to write a little VBA code to allow me to do this tedious task. This can be run by dragging and dropping the green Link External Data to Shapes Master shape onto the page (it is automatically deleted after it runs).
The code presents a dialog to offer the choices that I think are required.
- DataRecordset Column Name : This defaults to Process Step ID, but needs to be Name in this example
- Match Shape Data Row Label : Leave ticked if the shapes have a Shape Data row with the same Label as the Column Name
- Shape Data Row Name : Provide the name of the Shape Data Row to match if the Match Shape Data Row Label is unticked
- Inherited from Master Shape : Tick if the Shape Data Label or Name exists in the Master of the shapes to be matched. This needs to be unticked in this example because the simple Rectangle Master shape does not have any Shape Data Rows … they are all added to each shape by DV, and then by Custom Import.
The following screenshots show how easy it is to create data links to shapes added by Data Visualizer:
The VBA code
I have listed the key VBA functions below, and the code is available in the macro-enabled stencil, ExDataLinker.vssm that can also be downloaded as a zip file ( ExDataLinker.zip ). It shouuld be placed in yur My Shapes folder, and requires VBA for digitally signed macros to be enabled.
LinkShapes() sub routine
This is called via the LinkAllShapes(…) sub routine when the Link External Data to Shapes shape is dropped onto the page. It checks that the External Data window is open, and then opens the frmExDataLinker form is opened, and passes the values to the CheckInherited(…) or CheckLocal(…) functions.
Sub LinkShapes()
Dim drs As DataRecordset
Dim win As Visio.Window
Dim criteria As String
Dim frm As frmExDataLinker
Dim dataRowName As String
Dim columnName As String
Dim useLabel As Boolean
Dim inherited As Boolean
Dim shapesLinked As Integer
On Error GoTo errHandler
Set win = ActiveWindow.Windows.ItemFromID(Visio.VisWinTypes.visWinIDExternalData)
If win.Visible Then
Set drs = win.SelectedDataRecordset
If Not drs Is Nothing Then
Set frm = New frmExDataLinker
frm.TextBoxProp.Text = PROPNAME
frm.TextBoxColumn.Text = COLNAME
frm.CheckBoxUseLabel.Value = True
frm.Show
useLabel = frm.CheckBoxUseLabel.Value
columnName = frm.TextBoxColumn.Text
dataRowName = frm.TextBoxProp.Text
inherited = frm.CheckBoxInMaster.Value
If Not frm.Tag = "OK" Or Len(columnName) = 0 Then
Unload frm
MsgBox "The Column Name cannot be empty", vbExclamation, AppName
GoTo exitHere
ElseIf useLabel = False And Len(dataRowName) = 0 Then
Unload frm
MsgBox "The Shape Data Row Name cannot be empty if not matching the Column Name", _
vbExclamation, AppName
GoTo exitHere
Else
Unload frm
End If
If inherited Then
shapesLinked = CheckInherited(drs, columnName, useLabel, dataRowName)
Else
shapesLinked = CheckLocal(drs, columnName, useLabel, dataRowName)
End If
End If
End If
MsgBox shapesLinked & " shapes were linked to the external data recordset", _
vbInformation, AppName
exitHere:
Exit Sub
errHandler:
MsgBox Err.description, vbExclamation, AppName
Resume exitHere
Resume
End SubCheckInherited(…) function
This function gets the Shape Data row number from the Master shapes that contains the required row using GetRowWithLabel(…) or GetRowWithName(…) functions. It uses this to get the value in the Shape Data row, and then gets any rows from the DataRecordset that matches this value in the specified column. It then links the first matching DataRecordset row to the shape.
Private Function CheckInherited(ByVal drs As DataRecordset, ByVal columnName As String, _
ByVal useLabel As Boolean, ByVal dataRowName As String) As Integer
Dim lngRowIDs() As Long
Dim uniqueID As String
Dim mst As Visio.Master
Dim shp As Visio.Shape
Dim sel As Visio.Selection
Dim criteria As String
Dim dataRow As Integer
Dim counter As Integer
Dim undoScope As Long
undoScope = Application.BeginUndoScope(AppName)
On Error GoTo errHandler
For Each mst In ActiveDocument.Masters
If Not mst.Shapes(1).SectionExists(visSectionProp, Visio.visExistsAnywhere) = 0 Then
If useLabel Then
dataRow = GetRowWithLabel(mst.Shapes(1), columnName)
Else
dataRow = GetRowWithName(mst.Shapes(1), dataRowName)
End If
If dataRow > -1 Then
Set sel = ActivePage.CreateSelection(visSelTypeByMaster, 0, mst)
For Each shp In sel
uniqueID = shp.CellsSRC(visSectionProp, dataRow, visCustPropsValue).ResultStrU("")
If Len(uniqueID) > 0 Then
criteria = "[" & columnName & "] = '" & uniqueID & "'"
lngRowIDs = drs.GetDataRowIDs(criteria)
If UBound(lngRowIDs) > -1 Then
shp.LinkToData drs.ID, lngRowIDs(0), False
Debug.Print shp.Name & " linked to " & criteria
counter = counter + 1
End If
End If
Next shp
End If
End If
Next mst
CheckInherited = counter
exitHere:
Application.EndUndoScope undoScope, True
Exit Function
errHandler:
MsgBox Err.description, vbExclamation, AppName & ".CheckInherited"
Application.EndUndoScope undoScope, False
Resume exitHere
Resume
End Function
CheckLocal(…) function
This function gets the Shape Data row number of each shapes that contains the required row using GetRowWithLabel(…) or GetRowWithName(…) functions. It uses this to get the value in the Shape Data row, and then gets any rows from the DataRecordset that matches this value in the specified column. It then links the first matching DataRecordset row to the shape.
Private Function CheckLocal(ByVal drs As DataRecordset, ByVal columnName As String, _
ByVal useLabel As Boolean, ByVal dataRowName As String) As Integer
Dim lngRowIDs() As Long
Dim uniqueID As String
Dim mst As Visio.Master
Dim shp As Visio.Shape
Dim sel As Visio.Selection
Dim criteria As String
Dim dataRow As Integer
Dim counter As Integer
Dim undoScope As Long
undoScope = Application.BeginUndoScope(AppName)
On Error GoTo errHandler
For Each shp In ActivePage.Shapes
If Not shp.SectionExists(visSectionProp, Visio.visExistsAnywhere) = 0 Then
If useLabel Then
dataRow = GetRowWithLabel(shp, columnName)
Else
dataRow = GetRowWithName(shp, dataRowName)
End If
If dataRow > -1 Then
uniqueID = shp.CellsSRC(visSectionProp, dataRow, visCustPropsValue).ResultStrU("")
If Len(uniqueID) > 0 Then
criteria = "[" & columnName & "] = '" & uniqueID & "'"
lngRowIDs = drs.GetDataRowIDs(criteria)
If UBound(lngRowIDs) > -1 Then
shp.LinkToData drs.ID, lngRowIDs(0), False
Debug.Print shp.Name & " linked to " & criteria
counter = counter + 1
End If
End If
End If
End If
Next shp
CheckLocal = counter
exitHere:
Application.EndUndoScope undoScope, True
Exit Function
errHandler:
MsgBox Err.description, vbExclamation, AppName & ".CheckLocal"
Application.EndUndoScope undoScope, False
Resume exitHere
Resume
End FunctionGetRowWithName(…) function
This function returns the row number of the Shape Data row with the specified name, or -1 if it doesn’t exist.
Private Function GetRowWithName(ByVal shp As Visio.Shape, ByVal dataRowName As String) As Integer
On Error GoTo errHandler
Dim iRow As Integer
For iRow = 0 To shp.RowCount(Visio.visSectionProp) - 1
If shp.CellsSRC(visSectionProp, iRow, visCustPropsLabel).rowName = dataRowName Then
GetRowWithName = iRow
GoTo exitHere
End If
Next
GetRowWithName = -1
exitHere:
Exit Function
errHandler:
MsgBox Err.description, vbExclamation, AppName & ".GetRowWithName"
Resume exitHere
Resume
End FunctionGetRowWithLabel(…) function
This function returns the row number of the Shape Data row with the specified label, or -1 if it doesn’t exist.
Private Function GetRowWithLabel(ByVal shp As Visio.Shape, ByVal columnName As String) As Integer
On Error GoTo errHandler
Dim iRow As Integer
For iRow = 0 To shp.RowCount(Visio.visSectionProp) - 1
If shp.CellsSRC(visSectionProp, iRow, visCustPropsLabel).ResultStr("") = columnName Then
GetRowWithLabel = iRow
GoTo exitHere
End If
Next
GetRowWithLabel = -1
exitHere:
Exit Function
errHandler:
MsgBox Err.description, vbExclamation, AppName & ".GetRowWithLabel"
Resume exitHere
Resume
End FunctionRelated articles
Optimize Visio Flowcharts: Swimlane Reordering Tips
Microsoft Visio desktop Plan 2 and Professional editions provides the ability to create and synchronize cross-functional flowcharts between the diagram and an Excel table. This is great, and widely used for many types of processes. The Excel table normally has a Function / Swimlane column that contains text that becomes labels on the swimlane containers,…
New Requirement for VBA Digital Signatures in Visio
Like most developers, I have to buy a new digital certificate every 3 years to sign my Visio add-ins and VBA projects. Usually that means verifying my bone fides, paying the fee and downloading the certificate, but security has been increased, and now, like everyone else, I have to use a USB key with it…
Using Visio Color by Value on Connectors
Data Graphics in Visio Plan 2 and Visio Professional is great, but it only enables us to use them with 2D shapes in Visio, i.e. not on connectors. So, what if you want to change the line colour of the connectors between the 2D shapes because of the data flowing between them? Well, it is…
Counting glued and connected shapes in Visio
I got a surprise in one of my projects when I counted the shapes glued together using the Shape.GluedShapes(…) method … the sum of the filtered glued shapes just didn’t add up to the unfiltered count. So, I thought I should check the Shape.ConnectedShapes(…) method too … and there is a scenario where that has…
My new book on Visualizing Processes with Microsoft Visio has launched
Back in the early 1990s, there was an application called ABC Flowcharter that was the market leader for diagramming business flowcharts, but some of the brains behind Aldus PageMaker saw an opportunity to create something smarter, and left to write the Visio product, with the stated aim to overtake ABC Flowcharter within 2 years. They…
Pushing Data Visualizer in Visio beyond its limits
My last post was about some of the lessons learnt when trying to push Data Visualizer to its limits, but this one has some ways of overcoming these limitations. The main lesson learnt is that DV binds the shapes within the DV container shape, CFF Data Visualizer, and controls some of the ShapeSheet cells that…