Searching for unlinked rows in External Data

[WCTICK]

I have a Visio Org Chart containing approximately 1,000 employees that is linked to shape data via a Position Number.

After refreshing the data source, if new positions have been created or approved, those rows will be unlinked.

Is there an easy way to search for unlinked rows in External Data besides just scrolling through?  The External Data can be arranged by any of the data columns, but not the link status column that I can see.  Thanks.

[Thomas Winkel]

Is there an easy way to search for unlinked rows in External Data besides just scrolling through?

Not with on-board means, but with VBA:
http://visguy.com/vgforum/index.php?topic=3960.0

The External Data can be arranged by any of the data columns, but not the link status column that I can see.

Yes, it would be really helpful if MS could equip the external data window with an Excel-like auto-filter, including link status.

[wapperdude]

Presented here is adaptation of code developed by Thomas Winkel.  See his link provided above.  The code he provides works very well, and provides info on both linked and unlinked data sets, plus the shape to which the data is linked.  The modifications below were done to include the Page name and organize the data presentation according page.  To do so, required re-organizing the For...Next loops. 

Some simple testing was performed and compared with Thomas' code.  These matched identically with exception to the additional page information.

Enjoy.

Code Select Expand


Sub findUnlinkedData_WAP()
'This code is an adapted version of code created by Thomas Winkel
'Changes include identification of the Page being analyzed
'Re-configuration of the For...Loop nesting.
'
    Dim drs As Visio.DataRecordset
    Dim pge As Visio.Page
    Dim dataRowIDs() As Long
    Dim dataRowID As Variant
    Dim shapeIDs() As Long
    Dim test As Long
    Dim linked As Boolean
    Dim i As Integer
   
    For Each drs In ActiveDocument.DataRecordsets
        dataRowIDs = drs.GetDataRowIDs("")
        For Each pge In ActiveDocument.Pages
            Debug.Print pge.Name
            For Each dataRowID In dataRowIDs
                linked = False
                pge.GetShapesLinkedToDataRow drs.ID, dataRowID, shapeIDs
                On Error Resume Next
                test = UBound(shapeIDs) 'Causes an error if shapeIDs is not allocated
                If Err.Number <> 0 Then
'                    Msg = "Error # " & Str(Err.Number)
'                    MsgBox Msg
                    Debug.Print drs.Name & ": ID: " & dataRowID & " > not linked"
                    Err.Clear
                Else
                    linked = True
                    Debug.Print drs.Name & ": ID: " & dataRowID & " > linked to " & pge.Shapes.ItemFromID(shapeIDs(0)).Name
                End If
            Next dataRowID
        Next pge
    Next drs
End Sub


Below is sample output of new code as provided by the VBA Immediate Window.

Page-1
Sheet1$A1:C6: ID: 1 > linked to Sheet.99
Sheet1$A1:C6: ID: 2 > not linked
Sheet1$A1:C6: ID: 3 > not linked
Sheet1$A1:C6: ID: 4 > not linked
Sheet1$A1:C6: ID: 5 > not linked
Page-2
Sheet1$A1:C6: ID: 1 > not linked
Sheet1$A1:C6: ID: 2 > not linked
Sheet1$A1:C6: ID: 3 > not linked
Sheet1$A1:C6: ID: 4 > linked to Sheet.1
Sheet1$A1:C6: ID: 5 > linked to Sheet.9

[Thomas Winkel]

And here an adaption of Wapperdudes code to find all linked shapes, also if a row is linked to more shapes on the same page.
Also there is an alternative method to check for empty array without using error handling.

I wish all Visio enthusiasts all the best for 2024!

Code Select Expand


Sub findUnlinkedData_WAP()
    Dim drs As Visio.DataRecordset
    Dim pge As Visio.Page
    Dim dataRowIDs() As Long
    Dim dataRowID As Variant
    Dim shapeIDs() As Long
    Dim i As Integer
   
    For Each drs In ActiveDocument.DataRecordsets
        dataRowIDs = drs.GetDataRowIDs("")
        For Each pge In ActiveDocument.Pages
            Debug.Print pge.Name
            For Each dataRowID In dataRowIDs
                pge.GetShapesLinkedToDataRow drs.ID, dataRowID, shapeIDs
                If (Not shapeIDs) = -1 Then
                    Debug.Print "  " & drs.Name & ": ID: " & dataRowID & " > not linked"
                Else
                    For i = LBound(shapeIDs) To UBound(shapeIDs)
                        Debug.Print "  " & drs.Name & ": ID: " & dataRowID & " > linked to " & pge.Shapes.ItemFromID(shapeIDs(i)).Name
                    Next
                End If
            Next dataRowID
        Next pge
    Next drs
End Sub


[wapperdude]

Happy New Year Thomas!