Hyperlink to url returns error: cannot open the specified file

[Guest]

I've converted a column of URLs to hyperlinks using the suggestion at
http://www.microsoft.com/office/com...=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1
but now when I click on any of the cells containing the hyperlink to the URL,
I just get an error message which says: Cannot open the specified file.
What am I doing wrong?

 

[Guest]

:

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]

What was in the cell before you started?

If you look at the hyperlink (Insert|Hyperlink), what is the address do you see?

I'm wondering if you're missing the "Http://" (or mailto: or file:////)
portion????

 

[Dave Peterson]

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.

 

[Guest]

The cell contents originally were simply the url beginning with www. etc
I've put your VB routine in and run that, and now when I hover over the cell
the link begins with file:///\\www. etc whereas with the previously suggested
VB script, the link began with c:/// etc

I don't know anything about VB so I daren't fiddle with what you told me to
cut and paste.

I haven't yet tried using the worksheet function, I'll try that now.

 

[Guest]

Figured it out now, plucked up courage to change part of the script that was
causing the problem from 'file:////' to 'http://'and now it works fine, and
launches the browser, thanks!

 

[Brian G]

I am trying to have a hyperlink open a pdf file on my local machine for quick
viewing. I can't get it to work with PDFs that I have created with Adobe
acrobat or with my document scanner. I use the hyperlink wizard to set up
the link and when I try to click on the link I get the message "cannot open
the specified file"

I tried your "click me" routine and got the same message

Thanks for any advise that you can give me.