Macro to edit hyperlink address

[Corleone]

Not sure wherther this can be done, ill ask awa anyway

a colleague uses a document control system at work (project wise) and one of his tasks is to copy a set of hyperlinks from to the document control system into excel (for arguments sake they will appear in column A from row 10 down to row 100)

The issue he has is when the user click on the first hyperlink it works fine and send the user to the document in project wise.
however, when the user clicks on any subsequent hyperlink - it fails to work.

we have ascertained that the cause of this is because "version=" is added to the end of the address in the hyperlink , when this is removed manually the link then works.

ideally i would like to be able to run a macro which selects all the hyperlinks in column A , and removes "version=" from the address within the hyperlink.

any advice welcomed

thanks

 

[mole999]

record a find replace action

 

[Corleone]

record a find replace action

This wont work when accessing the addresses within hyperlinks - however i have"googled it and found a solution

ill post it tommorow when im back in work if anyones interested
cheers

 

[mole999]

must be the hyperlink format

    Columns("C:C").Select
    Selection.Replace What:="version=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[Corleone]

must be the hyperlink format

    Columns("C:C").Select
    Selection.Replace What:="version=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

This works if i want to replace the text displayed in the hyperlink, however, im referring to actually changing the address (ie the source from which the hyperlink comes from) this is the text in the address box at the bottom when you right click and edit the hyperlink.

thanks anyway (ill post my solution tommorow - file is at work)

 

[Corleone]

solution below



Sub HyperLinkChange()
Dim oldtext As String
Dim newtext As String
Dim h As Hyperlink

' These can be any text portion of a hyperlink, such as ".com" or ".org".
oldtext = "version="
newtext = ""

' Check all hyperlinks on active sheet.
For Each h In ActiveSheet.Hyperlinks
x = InStr(1, h.Address, oldtext)
If x > 0 Then
If h.TextToDisplay = h.Address Then
h.TextToDisplay = newtext
End If
h.Address = Application.WorksheetFunction. _
Substitute(h.Address, oldtext, newtext)
End If
Next
End Sub