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."
For nIndex = 1 To UBound(avLinks)
If StrComp(avLinks(nIndex), sLink, vbTextCompare) = 0 Then
nStatus = myworkbook.LinkInfo(sLink, xlLinkInfoStatus)
Select Case nStatus
sResult = "Copied values"
sResult = "Indeterminate"
sResult = "Invalid name"
sResult = "Missing file"
sResult = "Missing sheet"
sResult = "Not started"
sResult = "OK"
sResult = "Old"
sResult = "Source not calculated"
sResult = "Source not open"
sResult = "Source open"
sResult = "Unknown status code"
GetLinkStatus = sResult