Can you print a list of Links?

DD-SF

New Member
Joined
Dec 21, 2005
Messages
25
Is there a way to paste a list of the Links in a sheet somewhere, so that it can be easily seen, reviewed and printed out for an audit trail?

(i.e. an analogue to the F3-PasteList that gets you a list of the Names in a sheet.)

Thanks for any help.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Good evening DD-SF

Download and install my free add-in vi the link below. Once installed go to Ultimate > Formulae > Linked Formula Tools, ensure the drop down box is set to "Generate links reports for this workbook" and click OK.

HTH

DominicB
 
Upvote 0
Thanks - looked at your site, and it looks like a great set of tools.

However, is there a way to get just what I am looking to do without downloading the entire package - I want to do this at a corporate location, and will not be able to use an add-in to Excel. (I will certainly look at it for my personal at-home use.)
 
Upvote 0
1. Go Insert>Names>Define and in the Names in Workbook box type something like "lk" - w/o the quotes

2. In the Refers To box type:

=LINKS()

Click Add

3. Back in the spreadsheet, select an empty cell and type in the formula:

=INDEX(lk,ROWS($A$1:$A1))

and copy the formula down.

This will display all the external workbooks linked to (you'll get #REF errors when its run out of links).

Hope this helps!
 
Upvote 0
Neat trick, Richard. A real gem.

It works also with =names() in the defined name. When I hid some names, I needed to re-enter the formula for it to update. The hidden & system names (print area, filter database) did not show.

I guess there are other collections for which this works. Please post if you know some others. Cheers, Fazza
 
Upvote 0
Hi,

Code:
Sub Links()
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
    For i = 1 To UBound(aLinks)
        MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
    Next i
End If
End Sub
 
Upvote 0
1. Go Insert>Names>Define and in the Names in Workbook box type something like "lk" - w/o the quotes

2. In the Refers To box type:

=LINKS()

Click Add

3. Back in the spreadsheet, select an empty cell and type in the formula:

=INDEX(lk,ROWS($A$1:$A1))

and copy the formula down.

This will display all the external workbooks linked to (you'll get #REF errors when its run out of links).

Hope this helps!

This is a great little function and I'd love to know the logic behind the =links() in the defined names and whether there are other similar functions that could be used??
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top