Making unused data-linked SharePoint columns in Visio invisible automatically

I described how and why I sometimes link a SharePoint document library to Visio documents in a previous article ( see http://blog.bvisual.net/2015/02/10/displaying-sharepoint-document-library-column-values-on-visio-services-diagrams/). I mentioned that I always switch off the visibility of some columns because they are rarely needed in my Visio shapes as shape data rows. In this article, I present a VBA macro that can switch them off for you, therefore ensuring consistency.

The Link Data to Shapes feature in Visio does not allow for filtering of columns or rows when SharePoint lists are used as a data source. One answer is to create a view of the list in SharePoint to restrict the rows and some columns, but other columns still get pulled through to the External Data recordsets in Visio by default:

First, I created a constant in a VBA module which contains a semi-colon separated list of the columns that I want to hide:

Private Const mUnusedSPCols As String = "ID;Name;Keywords;Category;Content Type;File Size;App Created By;App Modified By;Workflow Instance ID;File Type;URL Path;Path;Item Type;Encoded Absolute URL"

Second, I wrote a function that returns the index of a column by Name (or DisplayName). This is necessary because I do not want an error that could be caused by returning the DataColumn for a name that does not exist.

Private Function getColumnIndexByName(ByVal drs As DataRecordset, _
    ByVal columnName As String) As Integer
Dim column As Integer
    getColumnIndexByName = -1
    For column = 1 To drs.DataColumns.Count
        If drs.DataColumns.Item(column).Name = columnName _
                Or drs.DataColumns.Item(column).DisplayName = columnName Then
            getColumnIndexByName = column
            Exit For
        End If
    Next column
End Function

Thirdly, I wrote the macro that loops through each of the column names in the mUnusedSPCols list and turns off their visibility.

Public Sub HideUnusedSPCols()
‘Get the datarecordset
Dim drs As DataRecordset
Dim drsExists As Boolean
Dim win As Visio.Window
    If Visio.ActiveDocument.DataRecordsets.Count = 0 Then
        Exit Sub
    End If
    Set win = Visio.ActiveWindow.Windows.ItemFromID(Visio.visWinIDExternalData)
    If drsExists = False Then
        Set drs = win.SelectedDataRecordset
    End If
    If drs Is Nothing Then
        ‘Abort if not present
        MsgBox "There is no active external data!", vbInformation
        Exit Sub
    End If
   
    If Len(mUnusedSPCols) = 0 Then Exit Sub
   
On Error GoTo errHandler
Dim i As Integer
Dim aryUnusedSPCols() As String
   
‘Enable diagram services
Dim DiagramServices As Integer
    DiagramServices = ActiveDocument.DiagramServicesEnabled
    ActiveDocument.DiagramServicesEnabled = visServiceVersion140 + visServiceVersion150

Dim UndoScopeID As Long
    UndoScopeID = Application.BeginUndoScope("Column Settings")
   
Dim vsoDataColumn As Visio.DataColumn
Dim colNumber As Long
Dim colName As String

    aryUnusedSPCols = Split(mUnusedSPCols, ";")
    For i = 0 To UBound(aryUnusedSPCols)
        colName = aryUnusedSPCols(i)
        colNumber = getColumnIndexByName(drs, colName)
        If colNumber > -1 Then
            Set vsoDataColumn = drs.DataColumns(colNumber)
            vsoDataColumn.Visible = "FALSE"
        End If
    Next
   
exitHere:
    Application.EndUndoScope UndoScopeID, True

    ‘Restore diagram services
    ActiveDocument.DiagramServicesEnabled = DiagramServices
    Exit Sub
errHandler:
    MsgBox Err.Description
    Resume exitHere
End Sub

The result is that the visibility of these columns is switched off:

It saves me time and ensures consistency, so I thought I would share it.