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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|