Updating Visio SharePoint Hyperlinks in Code

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”
    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
    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
End Sub

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: