Listing Checking the Status of All the Links in a Workbook

Sub CheckAllLinks(wb As Workbook) Dim avLinks As Variant Dim nLinkIndex As Integer Dim sMsg As String avLinks = wb.LinkSources(xlExcelLinks)

If IsEmpty(avLinks) Then

Debug.Print wb.Name & " does not have any links."

Else

For nLinkIndex = 1 To UBound(avLinks) Debug.Print "Workbook: " & wb.Name Debug.Print "Link Source: " & avLinks(nLinkIndex) Debug.Print "Status: " & _

GetLinkStatus(wb, CStr(avLinks(nLinkIndex)))

Next End If End Sub

To call CheckAllLinks from the ProcessFileBatch procedure, locate these statements shown in the ProcessFileBatch procedure:

' Code to process the file goes here

Debug.Print "If we wanted to do something to the " & _ "workbook, we would do it here."

Replace the Debug.Print statement with the following: CheckAllLinks wb

All the CheckAllLinks procedure does is call the GetLinkStatus function from Listing 6.6 for each link source found in the workbook. CheckAllLinks produced the following results when I ran it against some of my test files.

Opened workbook: Test.xls Workbook: Test.xls Link Source: C:

Status: Old

Closing workbook: Test.xls Opened workbook: NewLinkSource.xls NewLinkSource.xls does not have any links. Closing workbook: NewLinkSource.xls Opened workbook: NewLinkSourceII.xls NewLinkSourceII.xls does not have any links. Closing workbook: NewLinkSourceII.xls

Hopefully you are starting to see how easy it is to tie procedures together to do useful things. This allows you to break your programming tasks into small and easy-to-understand (and therefore code) pieces. Once you have all of the pieces, it is usually fairly easy to piece them all together.

0 0

Post a comment