can't find link to other workbook (i think)

Posted by S N on May 01, 2001 12:41 PM

I have a workbook that has a LOT of links in it (all within the same workbook)... but i think through a lot of cutting and pasting i somehow have a cell in the workbook that links to another workbook.

If i open the workbook and then attempt to immediately close it, the computer asks if i wish to save the changes (which is why i assume there is a link somewhere to another workbook, which "refreshes" everytime i open the workbook).

Is there any "easy" way to find this link, or any other potential reasons why a given cell may refresh upon opening (i do have a web-query in the workbook, BUT it is NOT set to refresh upon opening).

Thanks!

Posted by Dave Hawley on May 01, 2001 12:46 PM

Hi SN

Here is a macro I have written that will create a list of ALL external links in Formulas.

Sub ListExternalLinks()

'Written by OzGrid Business Applications
'www.ozgrid.com

'''''''''''''''''''''''''''''''''''''''''''''''''''''
'Creates a Worsheet called "Link List" and lists ALL _
external links in the Workbook.
'''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim sht As Worksheet
Dim LinkCells As Range, Cell As Range
'Add a new sheet to list all external links.
On Error Resume Next
Sheets.Add().Name = "Link List"
Application.DisplayAlerts = False
'If name does NOT = "Link List" then it already exists
If ActiveSheet.Name &LT;&GT; "Link List" Then ActiveSheet.Delete
Application.DisplayAlerts = True
'Clear column A and format as text.
Sheets("Link List").Columns(1).Clear
Sheets("Link List").Columns(1).NumberFormat = "@"
'Loop through each worksheet
For Each sht In ThisWorkbook.Worksheets
'Set "LinkCells" to range that has formulas
Set LinkCells = Nothing
Set LinkCells = sht.Cells.SpecialCells(xlCellTypeFormulas)
If Not LinkCells Is Nothing Then
'Loop through each cell in "LinkCells"
For Each Cell In LinkCells
'See if if an external link or not.
If Cell.Formula Like "[*" Then
'It is, so copy the formula to column A of "Link List"
Sheets("Link List").Cells _
(65536, 1).End(xlUp).Offset(1, 0) = Cell.Formula
End If
Next Cell
End If 'Not LinkCells Is Nothing
Next sht
End Sub

To use it, push Alt+F11 then go to Insert>module and paste in the code. Then Push Alt+Q and then Alt+F8, click "ListExternalLinks" and then Run.

Dave

OzGrid Business Applications

Posted by Mark W. on May 01, 2001 12:51 PM

S N, here's the Microsoft solution...

http://support.microsoft.com/support/kb/articles/Q188/4/49.ASP?LN=EN-US&SD=gn&FR=0&qry=dellinks&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97

Posted by Kevin James on May 01, 2001 12:53 PM

Links

S.N.:

I maintain a workbook whose internal links are date-based. Because date calculations need to be computed each time the workbook is open, even if all I do is open the book and immediately try to close it, I get prompted to save the file.

Since your workbook is making connections to the internet, I would venture that regardless of whether it is refreshed or not, the book acts as if there were changes.

As far as finding your links, try: Edit / Links. You'll see the list of links.