Link Status Checker : Workbook Links « Excel « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Excel
3. Workbook Links

Link Status Checker

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

    avLinks = ActiveWorkbook.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 = ActiveWorkbook.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 

 

Related examples in the same category