[Excel VBA] Basic Method to Create an HTML File and Export it as UTF-8

2025年12月27日

Introduction

There are times when you might want to export data managed in Excel as a simple web page or an HTML report. With VBA, you can construct HTML tags as strings and save them as an .html file.

In this article, I will explain the standard method for creating an HTML file using the ADODB.Stream object. This approach ensures the file is saved with UTF-8 encoding, preventing character encoding issues (Mojibake).

Complete VBA Code

First, here is the basic VBA code to create a simple HTML file. It includes the skeletal tags of an HTML document, such as <html>, <head>, and <body>.

Sub CreateSimpleHtmlFile()

    ' Declare variables
    Dim streamObj As Object
    Dim htmlFilePath As String
    
    ' Specify the path for the HTML file to be created
    htmlFilePath = ThisWorkbook.Path & "\MyReport.html"
    
    ' Create ADODB.Stream object
    Set streamObj = CreateObject("ADODB.Stream")
    
    ' --- 1. Configure Stream Object ---
    With streamObj
        .Type = 2 ' 2 = adTypeText (Text mode)
        .Charset = "UTF-8" ' Set character encoding to UTF-8
        .Open ' Open stream
        
        ' --- 2. Write HTML Content ---
        .WriteText "<html>" & vbCrLf
        .WriteText "<head>" & vbCrLf
        .WriteText "  <meta charset=""UTF-8"">" & vbCrLf
        .WriteText "  <title>Report Created from VBA</title>" & vbCrLf
        .WriteText "</head>" & vbCrLf
        .WriteText "<body>" & vbCrLf
        .WriteText "  <h1>This is a header created by VBA</h1>" & vbCrLf
        .WriteText "  <p>This is the first paragraph.</p>" & vbCrLf
        .WriteText "</body>" & vbCrLf
        .WriteText "</html>" & vbCrLf
        
        ' --- 3. Save to File and Cleanup ---
        .SaveToFile htmlFilePath, 2 ' 2 = adSaveCreateOverWrite (Overwrite)
        .Close ' Close stream
    End With
    
    ' Release object
    Set streamObj = Nothing
    
    MsgBox "HTML file creation complete."

End Sub

Key Points of the Code

1. Using the ADODB.Stream Object

While HTML files are technically text files, saving them with UTF-8 encoding is standard for correct web display. The biggest advantage of using ADODB.Stream is that you can easily create a UTF-8 file simply by specifying .Charset = "UTF-8".

2. Constructing and Writing HTML Code (.WriteText)

Creating an HTML file is a simple process of building HTML tags (like <h1> or <p>) as strings in VBA and writing them line by line using the .WriteText method. Appending vbCrLf to the end of each line adds a line break in the output HTML source code, making it readable for humans.

3. Saving to File and Cleanup (.SaveToFile)

The .SaveToFile method writes the contents of the memory stream to a physical file. Specifying 2 (adSaveCreateOverWrite) as the second argument ensures that if a file with the same name already exists, it will be automatically overwritten. Finally, always use .Close to close the stream and clean up.

Application: Converting Excel Data to an HTML Table

By applying this technique, you can output data from a specific Excel cell range as an HTML table (<table> tag).

Sub ExportRangeToHtmlTable()

    Dim streamObj As Object, sourceRange As Range, rw As Range, cl As Range
    Dim htmlFilePath As String
    
    htmlFilePath = ThisWorkbook.Path & "\DataTable.html"
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C5") ' Range to convert to table
    
    Set streamObj = CreateObject("ADODB.Stream")
    
    With streamObj
        .Type = 2
        .Charset = "UTF-8"
        .Open
        
        ' --- Basic HTML Structure ---
        .WriteText "<html><head><meta charset=""UTF-8""><title>Data Table</title></head><body>" & vbCrLf
        .WriteText "<h2>Data from Excel</h2>" & vbCrLf
        .WriteText "<table border=""1"" style=""border-collapse: collapse;"">" & vbCrLf
        
        ' --- Loop through Excel range to create table rows ---
        For Each rw In sourceRange.Rows
            .WriteText "  <tr>" & vbCrLf ' Row start tag
            For Each cl In rw.Cells
                .WriteText "    <td>" & cl.Value & "</td>" & vbCrLf ' Cell tag and value
            Next cl
            .WriteText "  </tr>" & vbCrLf ' Row end tag
        Next rw
        
        ' --- HTML End Tags ---
        .WriteText "</table>" & vbCrLf
        .WriteText "</body></html>" & vbCrLf
        
        .SaveToFile htmlFilePath, 2
        .Close
    End With
    
    Set streamObj = Nothing
    MsgBox "HTML table creation complete."
    
End Sub

This code dynamically builds an HTML table by looping through the Excel range A1:C5, generating <tr> (table row) tags for each row and <td> (table data) tags for each cell.

Summary

The fundamental concept of creating HTML files with VBA is to “construct HTML tags as strings and write them to a text file.”

When doing so, using the ADODB.Stream object is the most reliable and recommended method to ensure the file is saved in UTF-8, the web standard. Combining this with loops allows you to generate not just static pages, but dynamic HTML reports based on your Excel data.

よかったらシェアしてね！