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
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
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
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.
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
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