Finding a Row in a Visio DataRecordset

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")
        ‘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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


O365 and SharePoint

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

Azure Solutions for Office 365, and more...

Rob Fahrni



Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Smart graphics for visual people

%d bloggers like this: