add href hyperlinks to excel range programmatically

3
votes

This has got to be a common problem with a simple answer but I can't seem to turn up a solution. Using an Excel macro, I examine a website home page for links and put those links into a range in Excel.

Now I want to make those values into hyperlinks. 

Set allLinks = objIE.document.GetElementsByTagName("A")
For Each link In allLinks
    If InStr(link.href, inspectOneCat) Then
        inspectLink(linkCount) = link
        linkCount = linkCount + 1
    End If
Next

In the next step, the one-dimensional array is converted to a two-dimensional array with a descriptive column in inspectLink(i,0) and the link values in inspectLink(i,1). Then the array loaded into a range, like this:

        Sheets("Awesomepova").Range("a2:b300").Value = inspectLink

This works. But these links appear as values, not as hyperlinks. I want to do something like this:

    'Sheets("Awesomepova").Hyperlinks.Add Sheets("Awesomepova").Range("a2:b300"), Sheets("Awesomepova").Range("a2:b300").Value

This doesn't work. But I went into the worksheet manually and changed the first cell so it was hyperlinked and I noticed that even when I reload the entire range programmatically, the hyperlink remains, so I'm thinking this is a characteristic of the cell format, not the actual data in the cell.

Maybe the problem can be fixed by applying the same formatting to all the cells in the column where the rule is to use the cell value as the hyperlink value.

4
vbaexcel
Add your links to a different column (you cab hide this column if you want or have it in another hidden worksheet if you don't want the user to see it). Then use HYPERLINK in Column A to reference the links from hidden column. Should give you the desired results – Zac

4 Answers

4
votes

This is the equivalent using a For i... loop

Public Sub TestMe()
    Dim myArr As Variant
    Dim i As Long

    myArr = Array("www.bbc.com", "www.stackoverflow.com")

    For i = 0 To UBound(myArr)
        Worksheets(1).Cells(i + 1, 1).Hyperlinks.Add Worksheets(1).Cells(i + 1, 1), myArr(i)
    Next i
End Sub
2
votes

Considering that you already have crawled the websites in the array inspectLink, something like this works:

Public Sub TestMe()

    Dim myArr       As Variant
    Dim myUnit      As Variant
    Dim myCell      As Range

    myArr = Array("www.sugarpova.com", "www.stackoverflow.com")

    Set myCell = Worksheets(1).Cells(1, 1)

    For Each myUnit In myArr
        myCell.Hyperlinks.Add myCell, myUnit
        Set myCell = myCell.Offset(1, 0)
    Next myUnit

End Sub

It prints working hyperlinks on the first worksheet:

enter image description here

0
votes

Try looping through a2:b300 one hyperlink at a time using Hyperlinks.add instead of trying to force Hyperlinks.Add to apply to an entire range in one command:

For Row = 2 To 300

URL = Sheets("Awesomepova").Range("B" & Row).Value
Text = Sheets("Awesomepova").Range("A" & Row).Value

If URL <> "" Then
    Set result = Sheets("Awesomepova").Hyperlinks.Add(Range("C" & Row), URL, "", TextToDisplay:=Text)
End If

Next
0
votes

Several ways you can do the same. How about this?

Sub Demo()
    Dim storage As Variant, cel As Variant, r&

    storage = [{"https://www.google.com/", "https://www.yahoo.com/","https://www.wikipedia.org/"}]

    For Each cel In storage
        r = r + 1: Cells(r, 1).Hyperlinks.Add Cells(r, 1), cel
    Next cel
End Sub