Hyperlink to url returns error: cannot open the specified file
Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
With ActiveSheet
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
'delete existing hyperlinks in column A
myRng.Hyperlinks.Delete
For Each myCell In myRng.Cells
myCell.Hyperlinks.Add Anchor:=myCell, _
Address:="File:////" & myCell.Value, _
TextToDisplay:=myCell.Value
Next myCell
End Sub
I like to use the =hyperlink() worksheet function.
I'd put this in B1 (with the first filename in A1):
=hyperlink("file:////"&a1,"Click me!")
and drag down
I find the worksheet function easier to use and better behaved.
If you want to use this, but delete all the existing Insert|Hyperlinks versions,
you can do this:
Select the cells with the hyperlinks to delete
Hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
selection.hyperlinks.delete
And back to excel to see if they're gone.
browniebodrum wrote:
"browniebodrum" wrote:
PS When I hover my mouse over the cell with the malfunctioning hyperlink, I
can see that the hyperlink is prefixed with the address on my hard drive of
the directory in which the spreadsheet is saved, whereas if I type in a new
url in a different cell, the link is correct and works. But I don't want to
have to type in all the urls again...
--
Dave Peterson
|