Stripping out Hyperlinks in VBA
If I don’t document this here, it might not be anywhere. I searched for ages for a solution to this and could not find one anywhere, so I wrote this recursive function which takes care of the first part of the hyperlink and then enables me to just use Replace to get rid of the last tag. My sole contribution to the sum total of VBA programming out there.
strText = stripHyperlink(strText) strText = Replace(strText, "</A>", "") strText = Replace(strText, "</a>", "")
Function stripHyperlink(strSource)
' find first position of <A HREF tag p1 = InStr(1, UCase(strSource), "<A HREF") If p1 = 0 Then stripHyperlink = strSource Exit Function End If If p1 > 1 Then strLeft = Left(strSource, p1 - 1) p2 = InStr(p1 + 1, UCase(strSource), ">") strRight = Mid(strSource, p2 + 1) strSource = strLeft & " " & strRight ans = stripHyperlink(strSource) ' recurse Else p2 = InStr(p1 + 1, UCase(strSource), ">") strSource = Mid(strSource, p2 + 1) ans = stripHyperlink(strSource) End If
stripHyperlink = strSource End Function
Leave a Reply