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