3

I have a column of hyperlinks in an Excel file and I want to convert them to their respective HTML code:

<a href="http://www.example.com">Link Name</a>

I found ways to extract the link only (as text), but I need the whole HTML code as text to replace the hyperlink in the cell.

enter image description here

I've searched and searched but no one needed this answer, I guess. Can someone help?

Community
  • 1
  • 1
Alex
  • 1,427
  • 2
  • 13
  • 24
  • your link looks perfect! Please specify clearly what you have and what the result should look like. – ratmalwer Nov 06 '15 at 02:30
  • thanks for replyin, I added a screenshot to make it clear. I want to replace the hyperlink with its HTML code (in the Excel cell). – Alex Nov 06 '15 at 02:48

1 Answers1

3

It is actually a fairly straightforward method to yank the .Address and optional .SubAddress from the Hyperlinks collection object. The .TextToDisplay property is simply the value or text of the cell.

Sub html_anchors()
    Dim a As Range, u As String, l As String
    Dim sANCHOR As String: sANCHOR = "<a href=""%U%"">%L%</a>"

    For Each a In Selection
        With a
            If CBool(.Hyperlinks.Count) Then
                l = .Text
                u = .Hyperlinks(1).Address
                If Right(u, 1) = Chr(47) Then u = Left(u, Len(u) - 1)
                .Hyperlinks(1).Delete
                .Value = Replace(Replace(sANCHOR, "%U%", u), "%L%", l)
            End If
        End With
    Next a
End Sub

Select all of the cells you want to process and run the routine. If any cell in your selection does not contain a hyperlink, it will be ignored.

  • Awesome, Jeeped, thanks a lot! It's deffinitely not straightforward for me :) – Alex Nov 06 '15 at 02:55
  • One small problem - the code adds a trailing forward slash at the end of the URL, and my pdf files now give a 404 error because of it: a href="http:/www.example.com/file_2006.pdf/" Can you please help me with this? – Alex Nov 06 '15 at 03:00
  • Yep. I was worried that concatenating the address and subaddress was going to produce a double forward slash but didn't stop to think that `http://` NEEDS a double forward slah. Give me a sec... –  Nov 06 '15 at 03:02
  • hey, Jeeped, have you managed to fix the issue? Please let me know. Thanks. – Alex Nov 06 '15 at 11:16
  • The subaddress was doing nothing for your purposes and the variations of possible subaddress types were giving me conniptions so I removed them altogether. I may revisit this a little further down the road if I can manage to collect a reasonably complete assortment of subaddress styles but for now, this should serve your purposes. –  Nov 06 '15 at 11:25
  • It serves my purposes just fine. Let me say thank you again for your effort! – Alex Nov 06 '15 at 12:32