Ascertaining whether external data sources or hyperlinks exist

[Paul Martin]

Hi all

Is there an easy way to ascertain whether an Excel workbook has
external data sources through the Excel interface? I can, for example,
ascertain that a workbook has links because Links is enabled on the
Edit menu.

Similarly, is there a way to ascertain whether an Excel workbook uses
any hyperlinks?

I know how to do each of the above through VBA, but I want a means for
non-technical users to ascertain.

Thanks in advance

Paul Martin
Melbourne, Australia

 

[Leith Ross]

Hello Paul,

Maybe this will help. Add a VBA module to your Workbook and paste this
macro code into it. Open the Workbook and bring up the Macro Dialog, by
pressing ALT+F8. Select the macro and assign it a shortcut key. Inform
your users of the shortcut key and how to manually run the macro. The
macro counts the number of external links and hyperlinks within the
workbook and displays it for the user.


Code:
--------------------

Sub ShowLinkSummary()

Dim H As Long
Dim L As Long
Dim Lnk
Dim Wks As Worksheet

For Each Wks In ActiveWorkbook.Worksheets
H = H + Wks.Hyperlinks.Count
Next Wks

Lnk = ThisWorkbook.LinkSources
If Not IsEmpty(Lnk) Then L = UBound(Lnk)

Msg = "Workbook Link Summary:" & vbCrLf _
& "External Links = " & L & vbCrLf _
& "HyperLinks = " & H
MsgBox Msg

End Sub

 

[Paul Martin]

Hi Leith

Thanks, but I'm wondering if there's a non-VBA solution, similar to
Edit, Links.

So far, on any given large workbook, with large worksheets, there may
be several external data sources, but the only way I can ascertain
through the Excel interface the presence of the data is:
(1) the cursor is within the data table and then clicking the Data
menu. The Refresh Data item on the Data menu will be enabled
(2) perusing through the names in the Name Box and recognising the name
of a data source.

Neither of these means is adequate because the user may not have
created the workbook and so with (1) the user may not be able to locate
where the external data resides in order for the cursor to be within
the data, and (2) there may be a large number of names, and it may be
difficult to ascertain by this alone.

Any othe suggestions (non-VBA)?

Regards

Paul

 

[Leith Ross]

Hello Paul,

You have covered the non-VBA solutions. I can't think of any other
direct visual methods to confirm that links exist.

Sincerely,
Leith Ross