Excel VBA‎ > ‎

Excel Links and VBA

Links are one of the reason why Excel has become such an ubiquitous software. Links make refering to another component of a solution potentially almost magical.

One of my customer was facing an issue concerning links, they had to copy the contents of a spreadsheet into a template spreadsheet. That could take a long time since there were so many links to convert. The solution in this case was to loop through every range in the used range (Excel, special object, usedrange) and then test the existence of a formula. If the formula did contain a link, we had to copy it in our destination workbook.

That first step was to test the quality of the link. It's status is important to the extent that some links are broken, and not worth moving to another workbook. Other links refers to a missing worksheet and therefore should also be excluded.

Function GetLinkStatus(sLink As String, myworkbook As Workbook) As String
    Dim avLinks As Variant
    Dim nIndex As Integer
    Dim sResult As String
    Dim nStatus As Integer

    avLinks = myworkbook.LinkSources(xlExcelLinks)
    If IsEmpty(avLinks) Then
        GetLinkStatus = "No links in workbook."
        Exit Function
    End If
    For nIndex = 1 To UBound(avLinks)
        If StrComp(avLinks(nIndex), sLink, vbTextCompare) = 0 Then
            nStatus = myworkbook.LinkInfo(sLink, xlLinkInfoStatus)
            Select Case nStatus
            Case xlLinkStatusCopiedValues
                sResult = "Copied values"
            Case xlLinkStatusIndeterminate
                sResult = "Indeterminate"
            Case xlLinkStatusInvalidName
                sResult = "Invalid name"
            Case xlLinkStatusMissingFile
                sResult = "Missing file"
            Case xlLinkStatusMissingSheet
                sResult = "Missing sheet"
            Case xlLinkStatusNotStarted
                sResult = "Not started"
            Case xlLinkStatusOK
                sResult = "OK"
            Case xlLinkStatusOld
                sResult = "Old"
            Case xlLinkStatusSourceNotCalculated
                sResult = "Source not calculated"
            Case xlLinkStatusSourceNotOpen

                sResult = "Source not open"
            Case xlLinkStatusSourceOpen
                sResult = "Source open"
            Case Else
                sResult = "Unknown status code"
            End Select
            Exit For
        End If
    GetLinkStatus = sResult
End Function