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

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

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

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("Hyperlink 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

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("Hyperlink 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
>
>

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