Listing Updating Links with a New File LocationAn Alternative Procedure

Sub FixLinksII(wb As Workbook, sOldLink As String, sNewLink As String) Dim avLinks As Variant Dim nIndex As Integer

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

' if there are link sources, see if ' there are any named sOldLink If Not IsEmpty(avLinks) Then

For nIndex = 1 To UBound(avLinks) If _

StrComp(avLinks(nIndex), sOldLink, vbTextCompare) = 0 Then ' we have a match wb.ChangeLink sOldLink, sNewLink, xlLinkTypeExcelLinks ' once we find a match we ' won't find another, so exit the loop Exit For End If

Next End If

End Sub

So which is better? That depends on what you value. They're both pretty simple. The second is definitely longer, so you might be tempted to rule that one out. Are you wondering which one runs faster or if there is any difference? So was I. I dusted off the testing routines that you used to test screen updating and the status bar from the last chapter and adapted them to test FixLinks and FixLinksII. The results surprised me. I placed my bets on FixLinks assuming that the internal error-handling implementation was speedy and that less code would mean better performance. Not so. FixLinksII ran in nearly half the amount of time as FixLinks. My rapid assumption of less code = faster code ignored the reality that if you use FixLinks against a batch of files and most of the time there aren't even any links, then most of the time FixLinksII only executes two lines of code. One line to get the list of link sources, and another to see if any link sources were found. If no link sources are found, the procedure ends.

Another piece of functionality you'll need should check the status of links in a given workbook. To check the status of a link, use the LinkInfo method. Listing 6.6 presents a function you can use to check the status of a given link.

0 0

Post a comment