Excel Question

HomeLAN

HomeLAN

New Member
Using Office 97, how do you sever all links to external workbooks? There's no option for it that I can find in the Edit/Links menus.
 
One way is to copy all and paste special/values. Note that you lose internal formulas as well with this method, but I use it on reports that I have to e-mail to the satellite locations.
 
Using chcr's method, if you right click is there a "Paste special" option? If so, you may be able to paste the values and formulas.

I haven't used 97 in a while so I'm not sure.
 
chcr said:
One way is to copy all and paste special/values. Note that you lose internal formulas as well with this method, but I use it on reports that I have to e-mail to the satellite locations.
Click to expand...

I can do that, but I'd have to do it across the entire workbook - several sheets with many references - too many to individually sever this way. That would then eliminate some equations I'd like to preserve that don't refer to extrnal worksheets.

What I need is a command to globally break external links in a workbook - and I can't bloody find one.
 
greenfreak said:
Using chcr's method, if you right click is there a "Paste special" option? If so, you may be able to paste the values and formulas.

I haven't used 97 in a while so I'm not sure.
Click to expand...

Oh yeah, I know how to do this, but I'd rather not have to.
 
HomeLAN said:
I can do that, but I'd have to do it across the entire workbook - several sheets with many references - too many to individually sever this way. That would then eliminate some equations I'd like to preserve that don't refer to extrnal worksheets.

What I need is a command to globally break external links in a workbook - and I can't bloody find one.
Click to expand...

Yeah, I don't remember any but I didn't really start learning Excel until 2K. Sorry. :shrug:
 
http://www.mrexcel.com/td0008.html

How it works:
A dialogue box appears asking what types of files you want to search for links ( such as .xls). You need to be actually in the file to be searched. I tried all sorts of ways of searching for links such as D:\*.xls, and C:\*.xls when it didn't appear to clear all links. This showed me all links in a 50 sheet workbook, gave me the option of deleting each one and showed the complete path of each external link one at at time. It showed me links that I didn't even know existed. It worked efficiently, and on saving the workbook and opening again, all external links are now GONE!!.

It's an add-on...I'm not familiar with the source

or...
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 <> "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
Click to expand...
 
Ooh...even faster :)

How to Use the Edit Links Dialog Box

If cells in the workbook contain formulas that refer to external Excel workbooks, you can display these links and determine how they are updated by using the Edit Links dialog box. To use this dialog box: 1.On the Edit menu, click Links to open the Edit Links dialog box.2.In the Edit Links dialog box, a list of links to external workbooks is displayed. 3.To manually update all the data, click Update Values.4.To change the source of any link, first select the link that you want to change, and then click Change Source.

Select a new source, and then click OK.5.To open any source, first select the link that you want to open, and then click Open Source.6.To break the link with the external data source, click Break Link.

This command leaves the current data in the cells, but removes the reference to the external data.7.To check the status of any links and determine if they are still available, click Check Status.
 
Evidently you can't in 2K either. Here's a fix from Microsoft though. It d/ls a wizard specifically for Excel 97 & 2000.

Hope it helps.
 
There is no "Break Links" option anymore, Bish.

chcr, thanks. Frankly, this isn't even my workbook, so I think I'm going to tell the author that he's just shit out of luck.
 
You must log in or register to reply here.
Back
Top