[Solved] Determine if row is used (linked) - GetShapesLinkedToDataRow

[Ana]

The following code is intended to be the equivalent of opening the eternal data window, right-clicking a row, hovering over "Linked Shapes" and copying the relevant information. Even though I know that there is linked data in the document I get 0 returns.

Am I using incorrect parameters? Is there an alternative way of getting the linked shapes information?

Sub linkedData()

Dim vsoRowID As Long
Dim sIDS() As Long

'Initialize from external data, exit if none
Set vsoDocument = ActiveDocument
Set vsoExternalData = vsoDocument.DataRecordsets
If vsoExternalData.Count = 0 Then Exit Sub

' for each data set in the document
For Each vsoExternalDataSet In vsoExternalData
    ' get the IDs for the datat rows
    vsoRowIDs = vsoExternalDataSet.GetDataRowIDs("")
    ' for each row in External Data set (omit 1st row as it has column titles)
    For vsoRowID = LBound(vsoRowIDs) + 1 To UBound(vsoRowIDs)
        ' get the row information
        vsoRowData = vsoExternalDataSet.GetRowData(vsoRowIDs(vsoRowID))
        rowInfo = ""
        ' cycle through the foreground pages to see if the row is linked
        For Each p In vsoDocument.pages
            If p.Background = True Then Exit For
            p.GetShapesLinkedToDataRow vsoExternalDataSet.ID, vsoRowID, sIDS
' debug-only (set my breakpoint on page 5 that has 17 shapes linked to data, stepping through the 300+ rows I get 0 returns from GetShapesLinkedToDataRow, even on those rows I am certain there is a link)
If p.Index = 5 Then
    i = 0
End If
            'if row is linked then collect the applicable row information
            'If sIDS(0) Then
                If rowInfo = "" Then
                    rowInfo = vsoRowData(3) & " Rev " & vsoRowData(5) & vbTab & p.Index
                Else
                    rowInfo = rowInfo & ", " & p.Index
                End If
            'End If
        Next p
        Debug.Print vsoRowID & ". " & vsoRowData(0) & vbTab & rowInfo
    Next vsoRowID
Next vsoExternalDataSet

End Sub

[Jumpy]

Had no time to check or try your code, but what came to mind:

Code Select Expand


If p.Background = True Then Exit For


Every page in the loop that comes after a background page is omitted. That seems wrong.
Better sth. like:

Code Select Expand


If not p.Background Then
  p.GetShapesLinkedToDataRow vsoExternalDataSet.ID, vsoRowID, sIDS
  If p.Index = 5 Then
    i = 0
  End If 
  If rowInfo = "" Then
     rowInfo = vsoRowData(3) & " Rev " & vsoRowData(5) & vbTab & p.Index
  Else
     rowInfo = rowInfo & ", " & p.Index
  End If
End If


[Ana]

Thanks for the suggestion abou the exit for, I have thried both ways and both work (go through, print each page number), the only problem I have is I never get any retuns from GetShapesLinkedToDataRow.

Even copied the example from the reference page changed the row to a well-know linked row, and get an error (the example is not meant to handle rows that are not linked)

[Ana]

Got it ...

Sub linkedData()

Dim vsoRowID As Long
Dim sIDS() As Long

'Initialize from external data, exit if none
Set vsoDocument = ActiveDocument
Set vsoExternalData = vsoDocument.DataRecordsets
If vsoExternalData.Count = 0 Then Exit Sub

' for each data set in the document
For Each vsoExternalDataSet In vsoExternalData
    ' get the IDs for the datat rows
    vsoRowIDs = vsoExternalDataSet.GetDataRowIDs("")
    ' for each row in External Data set (omit 1st row as it has column titles)
    For vsoRowID = LBound(vsoRowIDs) + 1 To UBound(vsoRowIDs)
        ' get the row information
        vsoRowData = vsoExternalDataSet.GetRowData(vsoRowIDs(vsoRowID))
        rowInfo = ""
        ' cycle through the foreground pages to see if the row is linked
        For Each p In vsoDocument.pages
            If p.Background = True Then Exit For
            ' Iterate through all the shapes in the page
            Set vsoShapes = p.Shapes
            For Each vsoShape In vsoShapes
           
                If shapeData("Row ID", vsoShape) = vsoRowData(0) Then
                    If rowInfo = "" Then
                        rowInfo = vsoRowData(3) & " Rev " & vsoRowData(5) & vbTab & p.Index
                    Else
                        rowInfo = rowInfo & ", " & p.Index
                    End If
                End If
            Next vsoShape
        Next p
        Debug.Print vsoRowID & ". " & vsoRowData(0) & vbTab & rowInfo
    Next vsoRowID
Next vsoExternalDataSet

End Sub

Function shapeData(ByVal n, ByVal s As Visio.Shape)
shapeData = 0
If s.SectionExists(VisSectionIndices.visSectionProp, False) = True Then
    r = 0
    'For r = 0 To s.RowCount(VisSectionIndices.visSectionProp) - 1
        If Replace(s.CellsSRC(VisSectionIndices.visSectionProp, visRowProp + r, visCustPropsLabel).Formula, Chr(34), "") = n Then
            shapeData = s.CellsSRC(VisSectionIndices.visSectionProp, visRowProp + r, visCustPropsValue).Formula
            Exit Function
        End If
    'Next r
End If
End Function

In the function, the commented out loop can be used if you do not know the number of the row you're comparing against.