Visio is an enormous application with literally millions of lines of code, so it is inevitable that bugs creep in for time to time, and can lay dormant .. until you really need it! Such an incident happened to me during a project for a large organisation where the latest and greatest build and versions are held up by the internal IT departments until they are sure that there is absolutely nothing hidden inside. In this project, I was given a laptop with Visio Pro for Office 365 to work with … not a problem, even though that moniker already told me that this was a seriously out of date edition, since it has been called Visio Online Plan 2 for quite a while. Anyway, I developed a solution that relies heavily on linked SharePoint lists and document libraries, using a view. The automatic name of these views are very verbose, so I consistently renamed them. Also, I had the same SharePoint Document library view linked more than once, so that I could link multiple rows to shapes, so renaming was, and is, absolutely essential. Several weeks went by, and my solution was working fine, until one day last week, my colleague told me that the Data / Refresh All was failing for him, and for other users. A review of his laptop showed that his Visio version had been updated to build 1708 from 1609 and the refresh was reverting the carefully renamed data recordsets back to the underlying SharePoint view name. My own personal laptop, not the client’s, is at build number 1808, so I will get the real fix from Microsoft soon via the normal channel updates for Click-to-Run.
Well, this was disastrous, so I contacted the Microsoft Visio product team to report the issue immediately, stressing the urgency, and using my MVP credentials, of course. They quickly located the bug and fixed in internally, and it will be rolling out soon in the normal manner. There response was absolutely fantastic, and I would like to publicly thank them for reacting so effectively. However … the fix will not help for a while on this particular customer site as they are only just rolling out updates from a year ago! So, in the meantime, I have a problem, and possibly others out there in the wild may have a problem, but not yet realised it. Therefore, I have written some very simple macros that are activated by dragging and dropping one of two shapes onto a page, or from the macros ribbon button, in order to do the refresh, either for the active recordset in the External Data window, or for all data recordsets in the document. They simply get hold of the current name, refresh the recordset, and re-apply the name. Finally, the shapes delete themselves, and the master in the document.
The code is quite short and simple, and called by the drop event of the shapes, using the ShapeSheet formulas
The VBA code looks like this:
Option Explicit Public Sub RefreshActiveDRS(ByVal shpIn As Visio.Shape) RefreshActiveDataRecordset DeleteShapeAndMaster shpIn End Sub Public Sub RefreshAllDRS(ByVal shpIn As Visio.Shape) RefreshAllDatasets DeleteShapeAndMaster shpIn End Sub Private Sub DeleteShapeAndMaster(ByVal shpIn As Visio.Shape) On Error Resume Next Dim mst As Visio.Master If Not shpIn.Master Is Nothing Then Set mst = shpIn.Master End If shpIn.Delete If Not mst Is Nothing Then mst.Delete End If End Sub Public Sub RefreshActiveDataRecordset() 'Purpose: Refresh the active external data recordset keeping any rename 'Author : David J Parker, bVisual, 2018, no rights reserved On Error GoTo errHandler Dim doc As Document Set doc = Visio.ActiveDocument 'Enable diagram services Dim DiagramServices As Integer DiagramServices = doc.DiagramServicesEnabled doc.DiagramServicesEnabled = visServiceVersion140 + visServiceVersion150 'Get the datarecordset Dim drs As DataRecordset Set drs = Visio.ActiveWindow.Windows.ItemFromID( _ Visio.visWinIDExternalData).SelectedDataRecordset If drs Is Nothing Then 'Abort if not present MsgBox "There is no active external data!", vbInformation Exit Sub End If Dim existingName As String existingName = drs.Name drs.Refresh drs.Name = existingName exitHere: 'Restore diagram services doc.DiagramServicesEnabled = DiagramServices Exit Sub errHandler: MsgBox Err.Description Resume exitHere End Sub Public Sub RefreshAllDatasets() 'Purpose: Refresh the active external data recordset keeping any rename 'Author : David J Parker, bVisual, 2018, no rights reserved On Error GoTo errHandler Dim doc As Document Set doc = Visio.ActiveDocument 'Enable diagram services Dim DiagramServices As Integer DiagramServices = doc.DiagramServicesEnabled doc.DiagramServicesEnabled = visServiceVersion140 + visServiceVersion150 'Get the datarecordset Dim drs As DataRecordset Dim existingName As String For Each drs In doc.DataRecordsets existingName = drs.Name drs.Refresh drs.Name = existingName Next exitHere: 'Restore diagram services doc.DiagramServicesEnabled = DiagramServices Exit Sub errHandler: MsgBox Err.Description Resume exitHere End Sub
The only problem now is that this particular client does not normally allow any macros to be enabled!
Update : I have had to swap the original videos in this post to animated gifs because of security on this clients site!
The macros-enabled stencil is available on this link : Click Here
Steps to use this stencil:
- Drop this into your My Shapes folder
- When you have a data-linked document open ..
- Select More Shapes \ My Shapes \ RefreshData
i. The Refresh External Data stencil should open
ii. Enable macros if asked
- Drag and drop the green rectangle “Refresh Active Recordset” on to the page
i. It should refresh the active tab in the External Data window
ii. It should then delete itself
iii. The tab should be the correct name
- Drag and drop the amber rectangle “Refresh All Recordsets” on to the page
i. It should refresh all of the External Data window tabs
ii. It should then delete itself
iii. The tabs should all be the correct name
Update : If the dropped shape appears to do nothing, and does not delete itself, then I have also provided both an action tag and right mouse menu action to complete the process.