Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 15th 07, 11:52 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
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?

  #2   Report Post  
Old September 16th 07, 12:06 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
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   Report Post  
Old September 16th 07, 12:09 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
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   Report Post  
Old September 16th 07, 12:24 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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
  #5   Report Post  
Old September 16th 07, 12:58 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
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   Report Post  
Old September 16th 07, 03:46 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
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   Report Post  
Old May 14th 08, 05:10 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 03:44 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017