Excel links

[Bugga Letsky]

Hi
Does anybody know a way of 'cleaning' an Excel file, that
has got links 'stuck' in it?

i.e. some bug has been invoked whereby it keeps asking me
whether I want to update links to other workbooks, where
there are no links to other workbooks in the file.

 

[Ed Lisay]

Bugga,

I wote this last week, it seems like just what you need.
Sub removeHyperLinks()

'Ed Lisay
'September 3, 2003
'Purpose: Remove all the hyperlinks on the active worksheet
'Unfortunitly, it also removes the cell formating too


Dim mySheetName As String
Dim myLastCol As String
Dim myLastRow As String

'get the name of the active sheet
Range("A1").Select
mySheetName = ActiveCell.Worksheet.Name

'get the row and column of the last used cell
ActiveCell.SpecialCells(xlLastCell).Select
myLastCol = ActiveCell.Column
myLastRow = ActiveCell.Row

'loop through the sheet and remove all the hyperlinks
For rwIndex = 1 To myLastRow
For colIndex = 1 To myLastCol
With Worksheets(mySheetName).Cells(rwIndex,
colIndex)
.Hyperlinks.Delete 'funny this doesn't
error out if there is no link
End With
Next colIndex
Next rwIndex

Range("A1").Select
End Sub

 

[Robin Hammond]

Bugga,

There is a routine in my XspandXL add-in that should do the job for you. You
can download a time limited version off my site.

Robin Hammond
www.enhanceddatasystems.com
Check out our XspandXL add-in