Steps:
- Select the range of the table you want to convert.
- Loop through the rows and columns of the selected range to build the HTML table structure.
- Create an HTML file and write the generated table to this file.
- Save or open the HTML file so you can use or view it in a browser.
VBA Code to Convert an Excel Table to HTML
Sub ConvertTableToHTML()
' Declare variables
Dim ws As Worksheet
Dim tableRange As Range
Dim cell As Range
Dim html As String
Dim i As Long, j As Long
Dim htmlFilePath As String
Dim outputFile As Integer
' Define the worksheet and the range containing the table
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with the name of your sheet
Set tableRange = ws.Range("A1:C5") ' Replace "A1:C5" with the range of your table
' Initialize the HTML string
html = "<html>" & vbCrLf
html = html & "<head><title>Excel Table to HTML</title></head>" & vbCrLf
html = html & "<body>" & vbCrLf
html = html & "<table border='1' cellpadding='5' cellspacing='0'>" & vbCrLf
' Add the table headers (row 1)
html = html & "<tr>"
For j = 1 To tableRange.Columns.Count
html = html & "<th>" & tableRange.Cells(1, j).Value & "</th>"
Next j
html = html & "</tr>" & vbCrLf
' Add the data rows
For i = 2 To tableRange.Rows.Count
html = html & "<tr>"
For j = 1 To tableRange.Columns.Count
html = html & "<td>" & tableRange.Cells(i, j).Value & "</td>"
Next j
html = html & "</tr>" & vbCrLf
Next i
' Close the table and HTML tags
html = html & "</table>" & vbCrLf
html = html & "</body>" & vbCrLf
html = html & "</html>"
' Prompt the user to specify the file path to save the HTML file
htmlFilePath = Application.GetSaveAsFilename(FileFilter:="HTML Files (*.html), *.html")
' Check if the user selected a file location
If htmlFilePath <> "False" Then
' Open the file for writing
outputFile = FreeFile
Open htmlFilePath For Output As outputFile
' Write the HTML content to the file
Print #outputFile, html
' Close the file
Close outputFile
' Show confirmation message
MsgBox "The table has been successfully converted to HTML!", vbInformation
End If
End Sub
Explanation of the Code
- Declaring Variables:
- ws: Represents the worksheet containing the data.
- tableRange: Represents the range of the table (you can adjust this range as needed).
- html: Holds the structure of the HTML table.
- htmlFilePath: Stores the path where the HTML file will be saved.
- outputFile: Used to open and write to the HTML file.
- Building the HTML Structure:
- Headers: The code generates a <th> (table header) for each cell in the first row of the selected range.
- Data Rows: Each data row in the table is converted into an HTML <tr> (table row), with each cell becoming a <td> (table cell).
- Saving the HTML File:
- GetSaveAsFilename prompts the user to choose a file location and name for the HTML file.
- The HTML content is written to the file using the Print statement.
- Once saved, the file is closed, and a confirmation message is displayed.
Customization
- Table Range: Change the range Set tableRange = ws.Range(« A1:C5 ») to match the range of your table.
- Worksheet Name: Replace « Sheet1 » with the actual name of your worksheet.
- HTML Table Attributes: You can customize the appearance of the HTML table (e.g., adding colors, borders, etc.) by modifying the HTML code in the html variable.
How to Use
- Open the VBA editor in Excel (Press ALT + F11).
- In the Insert menu, choose Module to create a new module.
- Paste the code into the module.
- Run the ConvertTableToHTML macro to convert your Excel table into an HTML file.