A recent newsgroup post asked how to find a row in a Visio DataRecordset for a given criteria. Well, I have used the IT Asset Management sample in Visio 2007 Professional to demonstrate how this can be done with a VBA function.
Of course, you may wish to improve the user interface because I limited myself to using an InputBox, and I have assumed that there is only one column defining the primary key of the DataRecordset.
The sub function, FindRow, listed below first checks whether the External Data window is open, and then gets the active DataRecordset. The code then reads the columns of the DataRecordset and prompts for a selection of the number of the column that you wish to search for a value in.
The code will abort if an incorrect number is entered, otherwise you will be asked to enter the text to find in the column. Note that you can enter a wildcard “*” character.
You will then be prompted to enter the row number of the row that you want to navigate to in the External Data window. Note that you will not be prompted to select a row if there is only one matching row.
When you enter a row number, the code gets the related row ID, and uses this to select the row in the External Data window.
Here is a listing of the VBA code to find a row by criteria:
Public Sub FindRow()
‘David Parker : 11/11/2007
‘To find a row with a given string in a DataRecordset
Dim findString As String
Dim colString As String
Dim drs As DataRecordset
Dim col As DataColumn
Dim win As Visio.Window
Dim rowids() As Long
Dim i As Integer
Dim msg As String
Dim rowid As Long
Dim vData As Variant
Dim primKeys() As String
Dim primKey As Integer
Dim rowString As String
‘Check the active windows to find the window
For Each win In Visio.ActiveWindow.Windows
‘The External Data window has ID = 2044
If win.ID = 2044 Then
Set drs = win.SelectedDataRecordset
Exit For
End If
Next win
If drs Is Nothing Then
MsgBox “You do not have a recordset selected!”
Exit Sub
End If
‘Get the primary key (single assumed)
drs.GetPrimaryKey visKeySingle, primKeys
msg = “Enter the number of the column to search:”
i = 1
For Each col In drs.DataColumns
msg = msg & vbCrLf & CStr(i) & vbTab & col.Name
If primKeys(0) = col.Name Then
primKey = i
End If
i = i + 1
Next col
colString = InputBox(msg, , “1”)
If Not IsNumeric(colString) Then
MsgBox “You must enter a number between 1 and ” & drs.DataColumns.Count
Exit Sub
ElseIf CInt(colString) < 1 Or CInt(colString) > (drs.DataColumns.Count) Then
MsgBox “You must enter a number between 0 and ” & drs.DataColumns.Count
Exit Sub
End If
findString = InputBox(“Enter text to find”)
If Len(findString) = 0 Then
Exit Sub
End If
rowids = drs.GetDataRowIDs(drs.DataColumns.Item(CInt(colString)) & ” LIKE ‘” & findString & “‘”)
If UBound(rowids) > 0 Then
‘Need to ask which row
msg = “Select a row to highlight”
msg = msg & vbCrLf & “Row” & vbTab & drs.DataColumns(primKey).Name & _
vbTab & drs.DataColumns(CInt(colString)).Name
For i = 0 To UBound(rowids)
rowid = rowids(i)
vData = drs.GetRowData(rowid)
msg = msg & vbCrLf & CStr(i + 1) & vbTab & vData(primKey – 1) & _
vbTab & vData(CInt(colString) – 1)
Next i
rowString = InputBox(msg, , “1”)
Else
‘Only one row to select
rowid = rowids(0)
vData = drs.GetRowData(rowid)
rowString = “1”
End If
If Not IsNumeric(rowString) Then
MsgBox “You must enter a number between 1 and ” & UBound(rowids) + 1
Exit Sub
ElseIf CInt(rowString) < 1 Or CInt(rowString) > (UBound(rowids) + 1) Then
MsgBox “You must enter a number between 1 and ” & UBound(rowids) + 1
Exit Sub
End If
‘Select the row in the External Data window
win.SelectedDataRowID = rowids(CInt(rowString) – 1)
End Sub