Hyperlinks with Absolute Address

[ssb3e]

I have an Excel report 'Report A' which contains hyperlinks to other files. The hyperlink is also copied from 'Report A' to 'Report B' and other reports which are distributed to users. Therefore the hyperlink needs to contain the absolute address.

Excel replaces the absolute address with a relative address. For example, "\\Server01\Department01\Reports\Files\File 1.xlsm" is replaced with "..\Files\File 1.xlsm".
This happens when the hyperlink is created using the Hyperlink dialogue box and also when the hyperlink is created using VBA code:
ThisWorkbook.Worksheets("Report A").Hyperlinks.Add Cells(3, 1), "\\Server01\Department01\Reports\Files\File 1.xlsm"

I have tried creating the hyperlink in 'Report A' using =HYPERLINK("\\Server01\Department01\Reports\Files\File 1.xlsm", "Friendly Name"), but this does not copy to 'Report B'.
I need to copy the hyper link using VBA code.
Excel does not recognise it as a hyperlink, so the code Hyperlinks.Add cannot be used in copying it from 'Report A' to 'Report B'.
I tried using Workbook_B.Worksheets("Report B").Cells(1,1).Value = Workbook_A.Worksheets("Report A").Cells(3, 1).Value, but this copies the 'Friendly Name' from 'Report A' and not the hyperlink.

Can anyone help?

 

[Hughhh]

I've just now come across a similar problem. This worked for me:

Create your hyperlinks, and when saving your file, use Save As, then go to Tools > Web Options > Files > Uncheck "Update links on save"

Hope this works for you too.