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 https://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:

image

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:

image

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Chris Webb's BI Blog

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

davecra.wordpress.com/

Microsoft Office 365 Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com 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

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: