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






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

1.Checking the Status of All the Links in a Workbook
2.Updating Links with a New File Location