Make a Simple HTML Table from a Range
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
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.
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©¿©¬
Those are curly quotes above.
…mrt
And they shouldn’t be, and the parser ate the final <td> in the If IsNumeric part.
Dick -
This may have occurred to you:
Dim DataObj As New MSForms.DataObject”
…
DataObj.SetText sReturn
DataObj.PutInClipboard
…mrt
Michael
If you wrap your code in “code” tags it should work:
replace [ with < and ] with >
[code lang="html"][/code]
–>
[...] a comment.Download StatsHere are the download stats for the addins on the site. (table courtesy of DDoE)File NameDownloads Since [...]