Listing linked workbooks

C

callbr549

My workbook contains many links to external workbooks (about 20) maintained by others in my group. Each of the external workbooks gets updated monthly, and renamed. I manually change links as the new workbooks are posted.

I'm looking for a way to list the current filename of all of the external workbooks so that users can which version of the external files are being used.

I've tried using the cell("filename",reference) function, but this only works when the external workbooks are open. Once they are closed, the formula gives #NA. I have also played around with the Link Finder add in (Bill Manville and Associates, copyright 1997-2003), but this seems to only create a new workbook with all of the link data -- helpful for troubleshooting, but not what I'm looking for

Can anyone help me with this?
 
D

Dave Peterson

Do you use Edit|Links to change the source?
(just curious)

You can look there to see the links.

Or maybe a little macro will do it for you:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myLinks As Variant
Dim iCtr As Long

With ActiveWorkbook
myLinks = .LinkSources(Type:=xlExcelLinks)
End With

If IsArray(myLinks) Then
With Worksheets.Add
For iCtr = LBound(myLinks) To UBound(myLinks)
.Cells(iCtr, "A").Value = myLinks(iCtr)
Next iCtr
End With
End If

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
D

Dave Peterson

That makes sense to me, too.

In fact, there's another irritating dialog that drives me nuts.

Insert|Name|Define

If you use names in your workbooks and hate that dialog, you may, er, WILL want
to download Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager.

You can find it at:
NameManager.Zip from http://www.bmsltd.ie/mvp
 
Top