PDA

View Full Version : Solved: Why do my URL's change after saving a workbook?



MDN111
10-01-2007, 07:02 AM
Hello !
To make a serial number work as a hyperlink, I use de code below:

ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:=cAddress
To transform the serial number to a hyperlink I first activate the sheet, then the cell containing the serial number and finally I lauch the macro containing the code mentioned above. cAddress is a variable containing the URL.
All this works well just as long I don't save the workbook. When I open the "Edit hyperlink" dialog box, the URL is appears decently. See Voor-Saven.jpg.
When I save the workbook, It doesn't work anymore. If I click on the hyperlink, an error message appears saying the address is not valid. If I take a look at the "Edit hyperlink" dialog box again, the URL is not the same than it was before I saved the workbook. See Na-Saven.jpg.
Can anyone explain me what can cause such a problem and how to fix it?
Many thanks in advance,
MDN111.

malik641
10-01-2007, 06:40 PM
By any chance, is this workbook a "shared" workbook? By "Shared" I mean to say that there is a way to make an Excel workbook Accessible as Read-Write for multiple users when you convert a normal workbook into a "Shared" workbook. Have you done this with the workbook?

And Welcome to the forum, MDN111.

MDN111
10-02-2007, 02:48 AM
Hello malik641 !

Thank you for your reaction to my problem.

In the meantime I 've been looking around as well and found on several forums that other people also have this problem. However, my search was not useless and I finally stumbled across a site which offered me the solution:

jsbi.blogspot.com/2007/04/excel-tip-how-to-insert-hyperlinks-in.html

Reading all that, I discovered a weird way of handling hyperlinks by Excel. I suppose it all serves some kind of purpose but the meaning of it escapes me (due to my own lack of knowlegde).
However I could solve my problem by inserting the following code into the workbook:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'This prevents Excel from transforming absolute path to relative path.
Application.DefaultWebOptions.UpdateLinksOnSave = False
End Sub

Regards,
MDN111.

unmarkedhelicopter
10-02-2007, 05:45 AM
Thankyou for taking the time to update your thread and include your answer.
You can now mark your thread as solved.

malik641
10-02-2007, 05:50 PM
MDN111,

Thanks for the info. I have never seen that before.
Nice solution :thumb