View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default 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