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

Very Nice ,
I need help to put this table (HTML Table ) to Clipboard (Not simply text ). I want this table to paste in gmail new message.

For those who don't know how to run this VBA function. Open excel, then you have to open Visual Basic Editor (Windows: Alt+F11 / Mac: Tools -> Macro -> Visual Basic Editor). Insert a new module, cut all code in it then simply paste the code above, close VBA and run the function from some cell on your sheet =BuildHTMLTable(A1:D5).

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....