removing automatic links

[kirk]

Hi all, need some help with Excel and automatic links.

I'm doing work on a fairly large Excel file that my
workplace uses as a template. There are some
mysterious 'automatic links' that are occasionally
activated, but no one can remember why or how.

I've done some work to the template, and it now warns 'The
workbook you opened contains automatic links to
information in another workbook. Do you want to update?',
but the links are of no importance. They are actually
undesirable.

Is there a method of finding these links in order to
delete them, or someone disabling them?

thanks,
Kirk

 

[SA]

Kirk:

An external reference in a cell looks like this

=[Book1.xls]Sheet1!$B$4

A standard reference within the same file look like:

Sheet1!C4 or Sheet1!C4

To "automagically" remove the external references, you'd need to write a vba
routine loops through the sheets, rows and columns of each sheet and looks
at the formula's in the cells to determine if there's an external reference.
Something like the code below.

Steve Arbaugh
MS Access MVP

===========begin code==========
'Cut and paste me in a general module for the Workbook _
Run me from the immediate window.....
Function FindBookExtRefs()
Dim objRange As Range
Dim i As Integer
Dim j As Long

For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Activate
Set objRange = ActiveSheet.UsedRange
For i = 1 To objRange.Columns.Count
For j = 1 To objRange.Rows.Count
objRange(i, j).Select
If InStr(objRange(i, j).Formula, "[") > 0 Then 'there's an
external reference
If MsgBox("There's an external reference to: " & objRange(i,
j).Formula & " in cell: " & _
ActiveSheet.Name & " " & ActiveCell.Address & "; do you
want to delete it?", vbYesNo) = vbYes Then _
objRange(i, j).Formula = Null
End If
Next j
Next i
Next
End Function

=========end code========

 

[Guest]

Fantastic! It took a little tinkering, as most good
things do (had to swap i to Rows and j to Columns?, and
the usual non-VB-mind working out why it wouldn't work
etc) but it did the job brilliantly.

Thank you!

-----Original Message-----
Kirk:

An external reference in a cell looks like this

=[Book1.xls]Sheet1!$B$4

A standard reference within the same file look like:

Sheet1!C4 or Sheet1!C4

To "automagically" remove the external references, you'd need to write a vba
routine loops through the sheets, rows and columns of each sheet and looks
at the formula's in the cells to determine if there's an external reference.
Something like the code below.

Steve Arbaugh
MS Access MVP

===========begin code==========
'Cut and paste me in a general module for the Workbook _
Run me from the immediate window.....
Function FindBookExtRefs()
Dim objRange As Range
Dim i As Integer
Dim j As Long

For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Activate
Set objRange = ActiveSheet.UsedRange
For i = 1 To objRange.Columns.Count
For j = 1 To objRange.Rows.Count
objRange(i, j).Select
If InStr(objRange(i, j).Formula, "[") > 0 Then 'there's an
external reference
If MsgBox("There's an external reference to: " & objRange(i,
j).Formula & " in cell: " & _
ActiveSheet.Name & " " & ActiveCell.Address & "; do you
want to delete it?", vbYesNo) = vbYes Then _
objRange(i, j).Formula = Null
End If
Next j
Next i
Next
End Function

=========end code========

Hi all, need some help with Excel and automatic links.

I'm doing work on a fairly large Excel file that my
workplace uses as a template. There are some
mysterious 'automatic links' that are occasionally
activated, but no one can remember why or how.

I've done some work to the template, and it now warns 'The
workbook you opened contains automatic links to
information in another workbook. Do you want to update?',
but the links are of no importance. They are actually
undesirable.

Is there a method of finding these links in order to
delete them, or someone disabling them?

thanks,
Kirk

.