Yes, this is Excel. My apologies, as I thought I had
switched to the Excel group.
I started to install what you have suggested here, and
have a few problems.
I have never done the VBA general module before, so I went
into Macros, VB editor and put the code in. I am not sure
how to "run it from the immediate window", though I found
Immediate window, just didn't know what to do.
Also, I ran into a compile and then run error. Compile
was "Expecting FOR" or something, so I changed the code to:
End If
Exit For
End If
Next j
Next i
Next
End Function
at the end. I searched Help to figure out what to do
there. It compiled, so off I went.
Then I geta runtime error '1004'
Application-defined or Object-Defined Error
And I'm stumped.
Just in case, here's my code:
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(j, i).Select
If InStr(objRange(j, i).Formula, "[") > 0
Then 'there's an external reference
If MsgBox("There's an external reference
to: " & objRange(j, i).Formula & " in cell: " &
ActiveSheet.Name & " " & ActiveCell.Address & "; do you
want to delete it?", vbYesNo) = vbYes Then
objRange(j, i).Formula = Null
End If
Exit For
End If
Next j
Next i
Next
End Function
I put it in (General) Named FindBookExtRefs
in the VB editor.
Thanks - I am willing to learn what it takes to get rid of
these links - I can't see any and it's so annoying!!
Sara
-----Original Message-----
Sara:
Sounds like you are working with an Excel work book rather than anything
related to Access.
However, if you put the following vba code in an Excel general module and
run it from the immediate window it will search out external links and allow
you to delete them.
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(j, i).Select
If InStr(objRange(j, i).Formula, "[") > 0 Then 'there's an
external reference
If MsgBox("There's an external reference to: " & objRange(j,
i).Formula & " in cell: " & _
ActiveSheet.Name & " " & ActiveCell.Address & "; do you
want to delete it?", vbYesNo) = vbYes Then _
objRange(j, i).Formula = Null
End If
Next j
Next i
Next
End Function
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg
I have a workbook with NO links, at least no desired
links! I have tried the Methods on the info from the web
search and I've downloaded and tried the Wizard: Delete
LInks. No matter what, when I open the workbook, I still
see the message to update links.
Is there a way to fix this? Now that it's a new year, I
hope to not have to go through this every week for this
entire year.
Thank you,
Sara
.