Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() 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 -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=553094 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 wrote: 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 -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=553094 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() 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 -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=553094 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
External data not updating | Excel Discussion (Misc queries) | |||
Help - external data | Excel Discussion (Misc queries) | |||
create one pivot from mulitple data sources | Excel Discussion (Misc queries) | |||
Excel: Use a name with external workbook reference for data valida | Excel Worksheet Functions | |||
Why can't I edit my external data sources? | Excel Discussion (Misc queries) |