Hyperlink creation problem

[jerry.zornes]

Not really an excel guy so I need help. I have a dataset that is
imported to excel. One of the columns contains a hyperlink to an
external pdf file. When the spreadsheet is first opened the column
isn't recognized as a hyperlink. However if I click into it to edit it
then exit (without making any changes) the hyperlink becomes active
for as long as I have the workbook open. When the workbook is saved,
closed and reopened the hyperlink appears to be valid. However when
you click on it you get a 404 error. until you F2 and exit the field.
At which point it works. To say I'm completely stumped is an
understatement.

 

[Mike Fogleman]

Try this code to repair the hyperlinks. You didn't say what range they are
in so adjust the code to reflect your range.

Sub FixHyperlinks()
Dim Lrow As Long
Dim rng As Range
Dim c As Range
'change the B in the next 2 lines to your column
Lrow = Cells(Rows.Count, "B").End(xlUp).Row
Set rng = Range("B1:B" & Lrow)
For Each c In rng
If c.Hyperlinks.Count = 0 And Not c.Value = "" Then
ActiveSheet.Hyperlinks.Add Anchor:=c, Address:=c.Text
End If
Next c
End Sub

Mike F

 

[jerry.zornes]

Works perfectly the first time. It doesn't work on re-entry. Still get
a 404 error. But this is still good enough. I can make the spreadsheet
readonly and run this on open every time. Thanks very much for the
help.