Listing Programmatically Retrieving Link Source Information

Sub PrintSimpleLinkInfo(wb As Workbook) Dim avLinks As Variant Dim nIndex As Integer

' get list of Excel-based link sources avLinks = wb.LinkSources(xlExcelLinks) If Not IsEmpty(avLinks) Then

' loop through every link source For nIndex = 1 To UBound(avLinks)

Debug.Print "Link found to '" & avLinks(nIndex) & "'" Next nIndex

Else

Debug.Print "The workbook '" & wb.Name & _ "' doesn't have any links."

End If End Sub

As you can see, the only thing you need to check for when you're using LinkSources is to see if it returns Empty; this signifies that it didn't find any links. Because the only thing this procedure requires to run is a workbook parameter, this procedure would be an ideal candidate to call from the ProcessFileBatch procedure in Listing 6.1. To do this, locate the following line.

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

Replace that line with this line: PrintSimpleLinkInfo wb

Give it a whirl. Select a batch of Excel files including one that is linked to another workbook and check out the Immediate window. When I ran it on a batch of workbooks, I received the following output.

Opened workbook: Test.xls

Link found to 'C:\Chapter Six Examples.xls'

Closing workbook: Test.xls

Opened workbook: BTR Loader.xls

The workbook 'BTR Loader.xls' doesn't have any links.

Closing workbook: BTR Loader.xls

Opened workbook: Chapter Five Examples.xls

The workbook 'Chapter Five Examples.xls' doesn't have any links. Closing workbook: Chapter Five Examples.xls Opened workbook: Mike_Wileman_1-01-2004_BTR.xls

The workbook 'Mike_Wileman_1-01-2004_BTR.xls' doesn't have any links.

Closing workbook: Mike_Wileman_1-01-2004_BTR.xls

Opened workbook: OTA Reports.xls

The workbook 'OTA Reports.xls' doesn't have any links. Closing workbook: OTA Reports.xls

In later chapters, you'll learn how to produce a better looking display such as output to a worksheet. Are you starting to see how useful the ProcessFileBatch procedure is? Creating utilities to operate on workbooks can often be as simple as creating a simple procedure, as we did in Listing 6.3, and calling the procedure from within the ProcessFileBatch procedure.

Let's take this a little further. What if you move a workbook to a new file location and in the process break all of the links in any dependent files? You could manually open each dependent file and change the link source, but what fun would that be? Besides, it is so much easier and faster to create a simple utility to do it (see Listing 6.4).

0 0

Post a comment