Linking Data to Shapes in Visio after using Data Visualizer

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 Sub

CheckInherited(…) 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 Function

GetRowWithName(…) 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 Function

GetRowWithLabel(…) 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 Function

Related 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…