Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tracing external data links (2003)

    Hi All

    I have a spreadsheet which contains queries to an external data source. When I open the spreadsheet I am asked if I would like to enable or disable the automatic refresh of the queries.

    The problem I have is that the spreadsheet I am working with is an old one and there seems to be no obvious sheet that contains the external data query.

    Is there a way I can ask excel to show me where the external links are coming from so that I can cancel them?

    Kind regards
    Hayden

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tracing external data links (2003)

    You can take a look at Edit | Links...

    For more comprehensive info, download and install Bill Manville's free FindLink add-in from The Excel MVP Page.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracing external data links (2003)

    I suspect these are not listed under Edit links, since they are queries.

    Try this code:

    <pre>Sub test()
    Dim oSh As Worksheet
    Dim oQT As QueryTable
    For Each oSh In ActiveWorkbook.Worksheets
    If oSh.QueryTables.Count > 0 Then
    For Each oQT In oSh.QueryTables
    If MsgBox("Found a querytable in" _
    & vbNewLine & oSh.Name & vbNewLine & _
    "called :" & oQT.Name & "." & vbNewLine & _
    "Delete this query?", vbQuestion + vbYesNo) = vbYes Then
    oQT.Delete
    End If
    Next
    End If
    Next
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tracing external data links (2003)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Good point!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •