Process unlinked data in VBA

Started by Thomas Winkel, June 01, 2012, 03:37:35 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Thomas Winkel

Hi,

I have connected an Excel sheet to Visio.
Some of this data are linked to shapes, some are not (visible via chain symbol on the left).
I would like to have a loop through all data rows, check if it is linked or not, and do something if it is not linked.

Could someone give me a hint, please.

Best regards,
Thomas

aledlund

this posting was put together to try and address some of the datarecordset topics in visio
http://visguy.com/vgforum/index.php?topic=2298.msg10100#msg10100
It's got a lot of material that works with datarecordsets (the mechanism where visio stores the data linked information).
Take a look and come back if you want some more help.
al

Thomas Winkel

Thanks for the link, Al.
The video is interesting, but don't solve my problem.

Meanwhile I wrote some working code.
But I think it's kind of dirty.
So if there is a better way, I will be very interested.

Regards,
Thomas



Public Sub FindUnlinkedData()
    On Error Resume Next
    Dim vsoDataRecordset As Visio.DataRecordset
    Dim shapeIDs() As Long
    Dim dataRowIDs() As Long
    Dim dataRowID As Long
    Dim linked As Boolean
    Dim test As Long
    Dim PagObj As Visio.Page
   
    Set vsoDataRecordset = Visio.ActiveDocument.DataRecordsets(4)
   
    dataRowIDs = vsoDataRecordset.GetDataRowIDs("")
   
    For dataRowID = LBound(dataRowIDs) To UBound(dataRowIDs)
        linked = False
        For Each PagObj In ActiveDocument.Pages
            PagObj.GetShapesLinkedToDataRow vsoDataRecordset.ID, dataRowID, shapeIDs
            test = UBound(shapeIDs)
            If Err.Number Then
                Err.Clear
            Else
                linked = True
                Exit For
            End If
        Next PagObj
        If (Not linked) Then
            MsgBox dataRowID
        End If
    Next dataRowID
End Sub

Thomas Winkel

After so many years I found an error that gives correct results in only 95%. :o

The following code is supposed to give correct results in 99.99%: ;D


Sub findUnlinkedData()
    Dim drs As Visio.DataRecordset
    Dim pge As Visio.Page
    Dim dataRowIDs() As Long
    Dim dataRowID As Long
    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 i = LBound(dataRowIDs) To UBound(dataRowIDs)
            linked = False
            dataRowID = dataRowIDs(i)
            For Each pge In ActiveDocument.Pages
                pge.GetShapesLinkedToDataRow drs.id, dataRowID, shapeIDs
               
                On Error Resume Next
                test = UBound(shapeIDs) 'Causes an error if shapeIDs is not allocated
                If err.Number Then
                    err.Clear
                Else
                    linked = True
                    Exit For 'At least linked once
                End If
                On Error GoTo 0
               
            Next pge
           
            If (Not linked) Then
                Debug.Print drs.name & ": ID: " & dataRowID & " > not linked"
            Else
                Debug.Print drs.name & ": ID: " & dataRowID & " > linked to " & pge.Shapes.ItemFromID(shapeIDs(0)).name
            End If
        Next i
    Next drs
End Sub


P.S.
The error is here:
For dataRowID = LBound(dataRowIDs) To UBound(dataRowIDs)

I edit this old topic to document this for myself and for people who might find it via google.

wapperdude

Since my version of Visio doesn't support data recordsets, I can't play with the code and pretty much bypassed the original topic.  But, I believe LBound and UBound return integers.  That may be the problem in your original code as you declare:  Dim dataRowID As Long.  In your new code, you correct this.  So my observation is like closing the barn door after all the cows got out.   ::)

Anyway, great that you came back to this and updated the code!

Cheers!
Wapperdude

Visio 2019 Pro

Thomas Winkel

Hi Wapperdude,

in fact LBound and UBound returns long, so it may be better to declare i as long.
But this was not the problem.
The problem was that I considered the index of the array as its value.

Bug:
For dataRowID = LBound(dataRowIDs) To UBound(dataRowIDs)

Fixed:
For i = LBound(dataRowIDs) To UBound(dataRowIDs)
dataRowID = dataRowIDs(i)

More readable (dataRowID must be declared as variant):
For Each dataRowID In dataRowIDs

As dataRowID  is also a sequential number this bug gave correct results in most cases.

Cheers,
Thomas