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 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
    Application.EndUndoScope UndoScopeID, True

    ‘Restore diagram services
    ActiveDocument.DiagramServicesEnabled = DiagramServices
    Exit Sub
    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.


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: