In last month’s entry, I showed how you can connect Visio timelines to SharePoint calendars, but I was unable to provide VBA code for automatically update the hyperlinks because my laptop had some issues. Well, a month later, after a holiday and a back injury, I have provided some VBA code below that enables the hyperlinks to become usable with the SharePoint calendar used in the example.
Public Sub AmendLinks()
Const urlCell As String = “_VisDM_Encoded_Absolute_URL”
Const idProp As String = “Prop._VisDM_ID”
Const idProp As String = “Prop._VisDM_ID”
Dim shp As Visio.Shape
Dim pag As Visio.Page
Dim i As Integer
Dim urlOld As String
Dim id As String
Dim urlNew As String
Dim pag As Visio.Page
Dim i As Integer
Dim urlOld As String
Dim id As String
Dim urlNew As String
For Each pag In Visio.ActiveDocument.Pages
For Each shp In pag.Shapes
If shp.CellExistsU(“Prop.” & urlCell, Visio.visExistsAnywhere) _
And shp.CellExistsU(idProp, Visio.visExistsAnywhere) Then
urlOld = shp.Cells(“Prop.” & urlCell).ResultStr(“”)
id = shp.Cells(idProp).ResultStr(“”)
urlNew = Left(urlOld, Len(urlOld) – 5 – Len(Format(id, “#”))) & “DispForm.aspx?ID=” & Format(id, “#”)
shp.Cells(“Hyperlink.” & urlCell & “.Address”).FormulaU = “=””” & urlNew & “”””
End If
Next shp
Next pag
For Each shp In pag.Shapes
If shp.CellExistsU(“Prop.” & urlCell, Visio.visExistsAnywhere) _
And shp.CellExistsU(idProp, Visio.visExistsAnywhere) Then
urlOld = shp.Cells(“Prop.” & urlCell).ResultStr(“”)
id = shp.Cells(idProp).ResultStr(“”)
urlNew = Left(urlOld, Len(urlOld) – 5 – Len(Format(id, “#”))) & “DispForm.aspx?ID=” & Format(id, “#”)
shp.Cells(“Hyperlink.” & urlCell & “.Address”).FormulaU = “=””” & urlNew & “”””
End If
Next shp
Next pag
End Sub
