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.