A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Hyperlink to url returns error: cannot open the specified file



 
 
Thread Tools Display Modes
  #1  
Old September 15th 07, 11:52 PM posted to microsoft.public.excel.misc
browniebodrum
external usenet poster
 
Posts: 28
Default Hyperlink to url returns error: cannot open the specified file

I've converted a column of URLs to hyperlinks using the suggestion at
http://www.microsoft.com/office/comm...=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?
Ads
  #2  
Old September 16th 07, 12:06 AM posted to microsoft.public.excel.misc
browniebodrum
external usenet poster
 
Posts: 28
Default Hyperlink to url returns error: cannot open the specified file



"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...
  #3  
Old September 16th 07, 12:09 AM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default Hyperlink to url returns error: cannot open the specified file

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

browniebodrum wrote:
>
> I've converted a column of URLs to hyperlinks using the suggestion at
> http://www.microsoft.com/office/comm...=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?


--

Dave Peterson
  #4  
Old September 16th 07, 12:24 AM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
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
  #5  
Old September 16th 07, 12:58 AM posted to microsoft.public.excel.misc
browniebodrum
external usenet poster
 
Posts: 28
Default Hyperlink to url returns error: cannot open the specified file

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.
  #6  
Old September 16th 07, 03:46 AM posted to microsoft.public.excel.misc
browniebodrum
external usenet poster
 
Posts: 28
Default Hyperlink to url returns error: cannot open the specified file

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!
  #7  
Old May 14th 08, 05:10 PM posted to microsoft.public.excel.misc
brian g
external usenet poster
 
Posts: 2
Default Hyperlink to url returns error: cannot open the specified file

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.


"Dave Peterson" wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
OFFSET: Returns error when used to a linked file Arturo Excel Worksheet Functions 1 June 4th 07 11:48 PM
Can not open a file from hyperlink set Phuoc Nguyen Hanoi Links and Linking in Excel 0 November 28th 06 04:36 AM
Hyperlink won't open any other XLS file Jinx1966 Excel Discussion (Misc queries) 2 February 9th 06 08:26 AM
file open via IE hyperlink causes already open files to shrink and tile Marc Setting up and Configuration of Excel 0 May 4th 05 08:13 PM
hyperlink error: "Cannot open the specified file" Kate Choi Excel Worksheet Functions 1 January 18th 05 02:38 AM


All times are GMT +1. The time now is 08:41 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.