Hyperlink to url returns error: cannot open the specified file

B

browniebodrum

:

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...
 
D

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????
 
D

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.
 
B

browniebodrum

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.
 
B

browniebodrum

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!
 
B

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top