Seems following trick is working:
when you insert a dummy HYPERLINK FORMULA the style of the cell
remains "Hyperlink" and is not reset to Normal.

you loose the text formatting but the CELL formatting remains intact.

Sub Redo()
With ActiveCell
.Clear
.Hyperlinks.Add ActiveCell, "", "A1", , "CLICK ME"
.Font.Bold = True
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.Interior.ColorIndex = 3
.BorderAround , xlThick, xlColorIndexAutomatic, 7
End With
End Sub
Sub Remo()
Dim s$, b As Boolean, i As Boolean
With ActiveCell
s = .Hyperlinks(1).TextToDisplay
b = .Font.Bold
i = .Font.Italic
.Formula = "=HYPERLINK("""")"
.Value = s
.Font.Bold = b
.Font.Italic = i
End With
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Dave Peterson wrote :

I don't have a better way, but something that you might want to test.

Find a sacrificial cell.
copy the hyperlink cell to that cell
remove the hyperlink
copy|paste special|formats back

Clean up that sacrificial cell later.

(still a giant loop though.)

Or untested...
Insert a new worksheet
select all of the existing worksheet
copy
paste special|formats (to the new worksheet)
remove the links (all at once)

copy and paste|special back.

That might actually work.

(I didn't try it though. No sense both of us being disappointed!)





Lisa wrote:

I have javascript function that copies a table from a webpage to an
Excel worksheet. This is supposed to be a multi-purpose function
that works on various different tables, and sometimes the tables
have hyperlinks in them.

I have my script doing Hyperlinks.Delete(), and that strips all the
hyperlinks out of the sheet quite well. The problem is that when
the hyperlinks are removed, any formatting that existed in those
cells is removed as well. Borders, colors, font info, wrapping,
number formatting: all gone.

I tried doing this:

if (objExcelApp.Selection.Hyperlinks.Count 0){
for (i = objExcelApp.Selection.Hyperlinks.Count; i 0; i--){
LinkCell = objExcelApp.Selection.Hyperlinks(i).Parent;
NumFmt = LinkCell.NumberFormat;
IsBold = LinkCell.Font.Bold;
IsItalic = LinkCell.Font.Italic;
IsWrap = LinkCell.WrapText;
hAlign = LinkCell.HorizontalAlignment;
vAlign = LinkCell.VerticalAlignment;

LinkCell.Hyperlinks.Delete();

LinkCell.NumberFormat = NumFmt;
LinkCell.Font.Bold = IsBold;
LinkCell.Font.Italic = IsItalic;
LinkCell.WrapText = IsWrap;
LinkCell.HorizontalAlignment = hAlign;
LinkCell.VerticalAlignment = vAlign;
}
}

And it works. It doesn't do the borders, because I haven't done
that yet, but the basic concept works.

And it takes forever and a day.

So I was hoping that someone might know some arcane method of
removing hyperlinks without killing the formatting in the process.

Thanks,
Lisa