Listing Link Status Checker

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

' get a list of link sources avLinks = wb.LinkSources(xlExcelLinks)

' make sure there are links in the workbook If IsEmpty(avLinks) Then

GetLinkStatus = "No links in workbook." Exit Function End If

' default result in case the link is not found sResult = "Link not found."

For nIndex = 1 To UBound(avLinks) If _

StrComp(avLinks(nIndex), sLink, vbTextCompare) = 0 Then nStatus = wb.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

The longest part of this function is comparing the status code that LinkInfo returns against a list of possible codes so that you can translate the code into a human-friendly result. Otherwise, this procedure works like the other link oriented procedures—obtain a list of link sources, make sure the list isn't empty, and loop through the link sources returned until you find the one you're after.

Finally, before I wrap up the section on links I want to show you one more procedure, CheckAllLinks, that you can call from your ProcessFileBatch procedure (Listing 6.1). For now, CheckAllLinks outputs its results to the Immediate window (see Listing 6.7). Later in the book, you'll start outputting to Excel worksheets.

0 0

Post a comment