Make a Simple HTML Table from a Range

Tushar comments

1) Please consider posting the data in a table or attach a file. It’s a PITA to recreate the data set!

Very good point. I made this function.

Public Function MakeHTMLTable(rInput As Range, Optional bHeaders As Boolean = True) As String
   
    Dim rRow As Range
    Dim rCell As Range
    Dim sReturn As String
   
    sReturn = “<table>”
   
    If bHeaders Then
        sReturn = sReturn & “<tr><td> </td>”
       
        For Each rCell In rInput.Rows(1).Cells
            sReturn = sReturn & “<td>” & Chr$(rCell.Column + 64) & “</td>”
        Next rCell
       
        sReturn = sReturn & “</tr>”
    End If
   
    For Each rRow In rInput.Rows
        sReturn = sReturn & “<tr>”
       
        If bHeaders Then
            sReturn = sReturn & “<td>” & rRow.Row & “</td>”
        End If
       
        For Each rCell In rRow.Cells
            sReturn = sReturn & “<td>” & rCell.Text & “</td>”
        Next rCell
       
        sReturn = sReturn & “</tr>” & vbNewLine
    Next rRow
   
    sReturn = sReturn & “</table>”
   
    MakeHTMLTable = sReturn
   
End Function

With this data

this in the Immediate Window

produces this

C D
3 Name Number
4 Bob one
5 Jim two
6 bob three
7 bob four

And this in the Immediate Window

produces this

Name Number
Bob one
Jim two
bob three
bob four

Nothing fancy, but it gets the job done. Well almost. I got a little lazy converting my column numbers to letters. If I need to go beyond column Z, I’ll need to rewrite the code to use one of these.

Posted in Uncategorized

8 thoughts on “Make a Simple HTML Table from a Range

  1. Dick –

    Small improvements:

    Change sReturn=<table> to sReturn = <table border=1 rules=all cellpadding=”5?>

    In both IF bHeaders statements change your <td> to <th align=”center”> and your </td> to </th>

    In For each rCell in rRow.Cells add an IF

    IF IsNumeric(rcell.value) then
    sReturn = sReturn & “<td align = “right”>” & rCell.Text & “”
    Else etc.

    Thank you for this.

    …mrt©¿©¬

  2. Dick –

    This may have occurred to you:

    Dim DataObj As New MSForms.DataObject”

    DataObj.SetText sReturn
    DataObj.PutInClipboard

    …mrt

  3. Michael –

    If you wrap your code in “code” tags it should work:

    <!–
    replace [ with < and ] with >
    [code lang=”html”]
    –>

Leave a Reply

Your email address will not be published. Required fields are marked *