Convert Range To HTML Table Excel VBA Macro

In this post we see how to convert a range with data in Excel into an HTML table. We may want to get Excel data as an HTML table for example to add the table to a web page, to send an email with Excel data as a table in the body, or to show the data in a userform, among other things. We can see each separate use case in other posts. In this post, we will only see how to convert a selected range with data into an HTML table.

Macro code:

 
  Sub ConvertSheetRangeToHTML()
      Dim rng As Range, htmlstr As String, r As Integer, c As Integer
      Set rng = ActiveSheet.UsedRange
      r = 0
      If rng.Rows.Count > 1 Then
          htmlstr = "<table border=1 style='border-collapse: collapse'>"
          For Each rngrow In rng.Rows
              c = 0: r = r + 1
              htmlstr = htmlstr & "<tr>"
              For Each rngcol In rng.Columns
                  c = c + 1
                  rngvalue = Cells(r, c).Value
                  If r = 1 Then
                      htmlstr = htmlstr & "<th>" & rngvalue & "</th>"
                  Else
                      htmlstr = htmlstr & "<td>" & rngvalue & "</td>"
                  End If
              Next rngcol
              htmlstr = htmlstr & "</tr>"
          Next rngrow
          htmlstr = htmlstr & "</table>"
      End If
      Debug.Print htmlstr
  End Sub
 

Macro explained:

• First, we declare an object variable to store the range with data that we want to convert to HTML code. We also declare a string variable that stores the HTML code while looping through the data, and two integers to track the row and column.
• Then we define the source range with data. That could be the used range in a particular worksheet or a selected range.  If the range has more than 1 row, we can proceed to loop through the data.
• We are going to use two nested loops, the first one loops through each row in the range, and the second through each column inside a row. Before that, we need to initialize the htmlstr variable to open the HTML table tag.  To continue with the next steps we need to understand the structure of a basic HTML table – see an example below.

      <table>
      <tr><th>Name</th><th>Country</th></tr>  ‘first row
      <tr><td>Name</td><td>Country</td></tr>  ‘second row
      <tr><td>Name</td><td>Country</td></tr>
      </table>

 

• For each row in the range, we increment the variable r to keep the count of rows and add the starting HTML tag for table row (tr). We also need to reset the column count in variable c before starting the next loop through columns.
• For each column in a row, we increment the variable c to keep the column count and add the starting and closing HTML tag for table column. That’s either th (table heading) for cells in the first row or td (table cell) for cells in other rows.
• After the second loop, we close the HTML table row tag and move to the next row. After looping through all rows, we close the HTML table tag in the htmlstr variable. That variable stores now the range values in HTML format.
• We can add any styling elements inside the relevant HTML tags. For example, we could add borders to the table adding HTML or CSS styling as indicated below.

<table border=1 style='border-collapse: collapse'>

• We can use the string variable containing the HTML code to display the range of values as a web page document, to send the HTML table in the body of an email, or to display a table in a userform, among other things.

 

This is how we convert a range to HTML table in Excel with VBA macros.

 

Other examples:

• How to Export Table To Word with Excel VBA macros
• How to Add Button To Cell Right-Click Menu with Excel VBA macros
• How to Import Pictures From Google Drive UPDATE 2023 with Excel VBA macros