I am having a problem with Excel 2003 SP2 and Hyperlinks!
I created a macro in Excel 2000 that copied a workbook to another location then updated all the hyperlinks to suit, this is
Sub replacelink(years, week)
Dim c As Range
For Each c In Selection
Application.StatusBar = "Examining Row " & c.Row & " Coloumn " & c.Column & " In Sheet " & ActiveSheet.name
'if there is a hyperlink in the cell
If c.Hyperlinks.Count 0 Then
.Address = Replace(.Address, "2007", years)
.Address = Replace(.Address, "k09", "k" & week)
.Address = Replace(.Address, " 09", " " & week)
.Address = Replace(.Address, "Summary Data", "")
If c.HasFormula = True Then
oldform = c.Formula
oldform = Replace(oldform, "k09", "k" & week)
oldform = Replace(oldform, " 09", " " & week)
oldform = Replace(oldform, "2007", years)
c.Formula = oldform
This works great and alters the hyperlinks as required, however in excel 2003 the hyperlink gets truncated
../U3 East/2008/Week 13/01 - Monday Day - Wk13U3E.xls
//ebouds01/ltshare7/00000 - MU 3 Shift Reports/Summary Data/U3 East/2008/Week 13/01 - Monday Day - Wk13U3E.xls
And hence I cannot get at the hyperlink part “/Summary Data” to change it!!
I have tried setting the hyperlink
ActiveWorkbook.BuiltinDocumentProperties("Hyperlink Base") = "ebouds01ltshare70000 - MU 3 Shift ReportsSummary Data"
Which works on my PC but does not point to the same point on another PC.
Looking on the Microsoft website there is
a known bug with excel 2003 SP2 and Hyperlinks however I have tried there suggestion of installing SP3 and I still get the
Is there a way to get excel 2003 to display the entire Hyperlink address? So I can change what I need
or does anybody have any other ideas?
Thanks in advance for any help, because this is driving me up the wall!!!