Add hyperlink in exported HTML table with VBA

Question

I have an excel-sheet with 3 colums:

    A        B            C
  Item   Description   Hyperlink

I export this to a JSON-file and a HTML-file with a table where users can view the items in the JSON-file. This works pretty good for me. Now i'm stuck and don't know how to do my last improvement to this.

I want to have the hyperlink from column C, on the "item" from column A in the HTML-file. To export the HTML-file, I use the following code (which I find somewhere):

Sub Test()
    RangeToHtml Range("A2:B100"), "G:\PD\PW\Production webpagefiles\new\Overview_Table.html"
End Sub

Sub RangeToHtml(rng As Range, fileName As String)
    Dim resBeg As String
    resBeg = "<html><head><title>Overview</title><link rel=""stylesheet""  href=""Overview_Table_css.css"" type=""text/css"" media=""all"" /></head><body><table><tr class=""heading""><td>Item</td><td>Description</td></tr>"
    resEnd = "</table></body></html>"
    For i = 1 To rng.Rows.Count
        '---Rows---
        resBeg = resBeg & "<tr>"
        For j = 1 To rng.Columns.Count
            '---Columns---
            resBeg = resBeg & "<td>"
            resBeg = resBeg & rng.Cells(i, j).Value
            resBeg = resBeg & "</td>"
        Next j
        resBeg = resBeg & "</tr>"
    Next i
    Call SaveStringToFile(resBeg & resEnd, fileName)
End Sub

Sub SaveStringToFile(str As String, fileName As String)
    Open fileName For Output As #1
    Print #1, str
    Close #1
End Sub

Could anyone give me a shot in the right direction?

Xabier · Accepted Answer · 2018-08-30 10:49:12Z

1

I believe the following will do what you expect, it will create the table as previously but when creating the cells for the table in Column A, it will take the value from Column C and add it as a hyperlink to the newly created .html file:

Sub Test()
    RangeToHtml Range("A2:B100"), "G:\PD\PW\Production webpagefiles\new\Overview_Table.html"
End Sub

Sub RangeToHtml(rng As Range, fileName As String)
    Dim resBeg As String
    resBeg = "<html><head><title>Overview</title><link rel=""stylesheet""  href=""Overview_Table_css.css"" type=""text/css"" media=""all"" /></head><body><table><tr class=""heading""><td>Item</td><td>Description</td></tr>"
    resEnd = "</table></body></html>"
    For i = 1 To rng.Rows.Count
        '---Rows---
        resBeg = resBeg & "<tr>"
        For j = 1 To rng.Columns.Count
            '---Columns---
            If j = 1 Then 'if column 1 = A, then add hyperlink found in column C by offseting by 2 columns
                resBeg = resBeg & "<td><a href=" & rng.Cells(i, j).Offset(0, 2).Value & ">" & rng.Cells(i, j).Value & "</a></td>"
            Else
                resBeg = resBeg & "<td>" & rng.Cells(i, j).Value & "</td>"
            End If
        Next j
        resBeg = resBeg & "</tr>"
    Next i
    Stop
    Call SaveStringToFile(resBeg & resEnd, fileName)
End Sub

Sub SaveStringToFile(str As String, fileName As String)
    Open fileName For Output As #1
    Print #1, str
    Close #1
End Sub

answered Aug 30, 2018 at 10:49

Xabier

7,7251 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Xabier Over a year ago

@Danny1991 please could you mark my response as an answer if it has helped? Thanks. :)

Collectives™ on Stack Overflow

Add hyperlink in exported HTML table with VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related