Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 5
Default Hyperlink change after email...

Hello.
I have an excel sheet that has hyperlinks to other sheets. All sheets are on
a network share and permissoins are open to all.

When I attach and email the excel sheet (that contains the hyperlinks) from
the network share, the recipient receives the sheet and opens it, the
hyperlinks have all been changed and now point to the recipients C drive
Documents and Settings instead of the network share, hence not being able to
open the hyperlink.

If I copy that sheet from the network share and paste it to my desktop and
then attach and email it, everything works great.

Any ideas??? I tried this on both Excel 2000 and 2003. Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Hyperlink change after email...

The hyperlinks are being stored as relative.
To get them stored as absolute, open the workbook in Excel (while it
still has its links to the correct network share),
File Properties Summary Hyperlink base: \\NoServer\NoFolder
(or any other non-existent location).


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #3   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 5
Default Hyperlink change after email...

Thanks. But there was nothing in the Hyperlink base field. It was blank.

"Bill Manville" wrote:

The hyperlinks are being stored as relative.
To get them stored as absolute, open the workbook in Excel (while it
still has its links to the correct network share),
File Properties Summary Hyperlink base: \\NoServer\NoFolder
(or any other non-existent location).


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


  #4   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Hyperlink change after email...

Asif Shah wrote:
Thanks. But there was nothing in the Hyperlink base field. It was blank.

Exactly.
That's why you have the problem and why I suggested you fill it with some
non-existent location such as \\NoServer\NoFolder.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #5   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 5
Default Hyperlink change after email...

Oh OK.
I entered that in and now the link dosent even work from the network share
even before emailing. I get a message saying:

The address of this site is not valid. Check the address and try again.

"Bill Manville" wrote:

Asif Shah wrote:
Thanks. But there was nothing in the Hyperlink base field. It was blank.

Exactly.
That's why you have the problem and why I suggested you fill it with some
non-existent location such as \\NoServer\NoFolder.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup




  #6   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Hyperlink change after email...

Whoops - sorry - yes.
What I should have said was "to avoid the problem you need to set an
impossible base before creating the hyperlinks". That will cause the
hyperlinks to be created as absolute rather than relative.

Running the following macro should do the trick of converting the
relative links to absolute:

Sub FixHyperLinks()
Dim WS As Worksheet
Dim H As Hyperlink
ActiveWorkbook.BuiltinDocumentProperties("Hyperlin k Base") = _
"\\NoServer\NoFolder"
For Each WS In ActiveWorkbook.Worksheets
For Each H In WS.Hyperlinks
If Mid(H.Address, 2, 1) < ":" And _
Left(H.Address, 2) < "\\" Then
H.Address = ActiveWorkbook.Path & "\" & H.Address
End If
Next
Next
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #7   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 5
Default Hyperlink change after email...

I added the hyperlink base first and then created the hyperlinks in the sheet
and that worked. But this is something we have to do each time. This never
used to happen before.
Will the macro take care of it permanantly? Where do I enter it in?
Thanks.

"Bill Manville" wrote:

Whoops - sorry - yes.
What I should have said was "to avoid the problem you need to set an
impossible base before creating the hyperlinks". That will cause the
hyperlinks to be created as absolute rather than relative.

Running the following macro should do the trick of converting the
relative links to absolute:

Sub FixHyperLinks()
Dim WS As Worksheet
Dim H As Hyperlink
ActiveWorkbook.BuiltinDocumentProperties("Hyperlin k Base") = _
"\\NoServer\NoFolder"
For Each WS In ActiveWorkbook.Worksheets
For Each H In WS.Hyperlinks
If Mid(H.Address, 2, 1) < ":" And _
Left(H.Address, 2) < "\\" Then
H.Address = ActiveWorkbook.Path & "\" & H.Address
End If
Next
Next
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


  #8   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Hyperlink change after email...

Asif Shah wrote:
Will the macro take care of it permanantly?

Yes

Where do I enter it in?

In the VB editor, in a standard module

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

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
email hyperlink Lidia Excel Discussion (Misc queries) 0 April 6th 08 04:59 PM
How to Hyperlink in an email? pokdbz Excel Discussion (Misc queries) 2 November 7th 07 05:35 PM
Email via hyperlink Paul Dye Excel Discussion (Misc queries) 0 November 7th 06 11:27 PM
Excel Email Hyperlink Bug jcpotwor Excel Discussion (Misc queries) 0 April 21st 06 02:17 PM
email using hyperlink crazylikeanut Excel Worksheet Functions 1 November 30th 05 08:23 PM


All times are GMT +1. The time now is 06:57 PM.

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

About Us

"It's about Microsoft Excel"