Convert an Excel Range to a Bootstrap HTML table

gistfile1.vb

' Example function call: =BuildHTMLTable(A1:D5)

Public Function BuildHTMLTable(rng As Range) As String
' Given a Range of Cells, build a Bootstrap HTML table, using the formatting
' specified in the Excel cells. If "header" is specified to equal true, assumes
' the first row in the table is a header row.
    Dim last_r As Long: last_r = rng.Cells(1, 1).Row
    Dim tds As New Collection
    Dim txt As String
    Dim isFirstRow As Boolean: isFirstRow = True
    Dim cell As Range, r As Long
   
    txt = "<table class=" & Chr(34) & _
          "table table-compressed table-striped" & Chr(34) & ">" & vbNewLine
    For Each cell In rng
        r = cell.Row
        If (r <> last_r) Then
            If isFirstRow Then
                txt = txt & vbTab & "<thead>" & vbNewLine & BuildRow(tds, isFirstRow) & vbTab & _
                                    "</thead>" & vbNewLine & vbTab & "<tbody>" & vbNewLine
            Else
                txt = txt & BuildRow(tds, isFirstRow)
            End If
            isFirstRow = False
            Set tds = New Collection
        End If
        tds.Add cell.Text
        last_r = r
    Next
    txt = txt & BuildRow(tds, isFirstRow)
    txt = txt & vbTab & "</tbody>" & vbNewLine & "</table>" & vbNewLine
    BuildHTMLTable = txt
End Function

Private Function BuildRow(tds As Collection, header As Boolean) As String
    ' Build a single HTML row given a collection of tds
    Dim txt As String: txt = vbTab & vbTab & "<tr>"
    Dim start_tag As String, end_tag As String, td As Variant
    If header Then
        start_tag = "<th>"
        end_tag = "</th>"
    Else
        start_tag = "<td>"
        end_tag = "</td>"
    End If
    For Each td In tds
        txt = txt & start_tag & td & end_tag
    Next
    txt = txt & "</tr>" & vbNewLine
    BuildRow = txt
End Function

Sorry to revive an old thread - I use this function and love it!

I was wondering if there may be any way to amend the function such that hyperlinks in Excel cells are retained.

So, for a cell containing the text 'Google' with hyperlink to 'www.google.com', instead of outputting:
<td>Google</td>

What I am ideally hoping for instead is this:
<td><a href="www.google.com" target="_blank">Google</a></td>

Hey there. Thanks for the code. But it is working only for the first 40 rows in my table. The rest (~ 110 rows) creates an output error. Why is that? Special symbols in the cell's text? Or any other limitations of the macro? I wish I had free time to convert your code to the regular macro and debug it on the problematic range....