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

Next

GetLinkStatus = sResult

End Function