【Excel VBA】ワークシートをテンプレートとしてHTMLレポートを自動生成する方法

VBAで複雑なHTMLレポートを作成する際、すべてのHTMLコードをVBAのコード内に文字列として記述するのは、非常に非効率でメンテナンスも困難です。

そこで今回は、HTMLの骨格を別のワークシートに「テンプレート」として記述しておき、VBAでそのテンプレートを読み込んで、動的なデータや表を埋め込んでいくという、非常にスマートで管理しやすいレポート生成のテクニックを解説します。

---

準備：データシートとテンプレートシート

この手法では、Excelブック内に最低2枚のワークシートを用意します。

1. データシート (例: DataSheet): レポートに埋め込みたい元データが配置されているシートです。
2. テンプレートシート (例: TemplateSheet): HTMLの雛形を記述しておくシートです。A列に上から順にHTMLコードを一行ずつ記述します。データや表を埋め込みたい場所には、__TITLE__や__MAIN_CONTENT__のような**目印（プレースホルダ）**を記述しておきます。

---

完成したVBAコード（全ヘルパー関数＋メイン処理）

この記事で紹介するメインの処理は、これまでの記事で作成したヘルパー関数群を利用します。以下に、必要な全ての関数と、それらを使ってレポートを生成するメインプロシージャの全コードを掲載します。

' =================================================================
' === ヘルパー関数群 (これまでの記事で作成) ===
' =================================================================
Function EncodeToHtml(ByVal inputText As String) As String
    Dim dom As Object
    Set dom = CreateObject("MSXML2.DOMDocument.6.0")
    dom.LoadXML "<temp />"
    dom.FirstChild.Text = inputText
    EncodeToHtml = Replace(dom.FirstChild.FirstChild.xml, vbCrLf, "<br />")
    Set dom = Nothing
End Function

Function WrapWithTag(ByVal tagName As String, ByVal content As String) As String
    WrapWithTag = "<" & tagName & ">" & content & "</" & tagName & ">"
End Function

Function AddAttribute(ByVal elementString As String, ByVal attrName As String, ByVal attrValue As String) As String
    Dim dom As Object
    Set dom = CreateObject("MSXML2.DOMDocument.6.0")
    dom.LoadXML elementString
    dom.FirstChild.setAttribute attrName, attrValue
    AddAttribute = dom.FirstChild.xml
    Set dom = Nothing
End Function

Function GenerateHtmlElement(ByVal content As String, Optional ByVal tagName As Variant, Optional ByVal attributes As Variant) As String
    Dim htmlString As String, i As Long
    htmlString = EncodeToHtml(content)
    If IsMissing(tagName) = False Then
        htmlString = WrapWithTag(CStr(tagName), htmlString)
        If IsMissing(attributes) = False Then
            For i = 0 To UBound(attributes)
                htmlString = AddAttribute(htmlString, CStr(attributes(i)(0)), CStr(attributes(i)(1)))
            Next i
        End If
    End If
    GenerateHtmlElement = htmlString
End Function

Function RangeToHtmlTable(ByVal sourceRange As Range) As String
    Dim dom As Object, tableNode As Object, rowNode As Object, cellNode As Object, rowNum As Long, colNum As Long
    Set dom = CreateObject("MSXML2.DOMDocument.6.0")
    dom.LoadXML "<table border='1' style='border-collapse: collapse;' />"
    Set tableNode = dom.FirstChild
    For rowNum = 1 To sourceRange.Rows.Count
        Set rowNode = tableNode.appendChild(dom.CreateElement("tr"))
        For colNum = 1 To sourceRange.Columns.Count
            Set cellNode = dom.CreateElement(IIf(rowNum = 1, "th", "td"))
            cellNode.Text = sourceRange.Cells(rowNum, colNum).Text
            rowNode.appendChild cellNode
        Next colNum
    Next rowNum
    RangeToHtmlTable = tableNode.xml
    Set cellNode = Nothing: Set rowNode = Nothing: Set tableNode = Nothing: Set dom = Nothing
End Function

' =================================================================
' === メイン処理：テンプレートからHTMLレポートを作成 ===
' =================================================================
Sub CreateReportFromTemplate()
    Dim streamObj As Object
    Dim templateSheet As Worksheet
    Dim dataSheet As Worksheet
    Dim templateHeader As String
    Dim templateFooter As String
    Dim finalHtml As String
    Dim outputFilePath As String

    ' --- 1. 各オブジェクトと変数を設定 ---
    Set templateSheet = ThisWorkbook.Worksheets("TemplateSheet")
    Set dataSheet = ThisWorkbook.Worksheets("DataSheet")
    outputFilePath = ThisWorkbook.Path & "\FinalReport.html"

    ' --- 2. テンプレートの読み込みとプレースホルダの置換 ---
    ' TransposeとJoinでテンプレートシートのA列を高速で文字列に結合
    templateHeader = Join(WorksheetFunction.Transpose(templateSheet.Range("A1:A10").Value), vbCrLf)
    templateFooter = Join(WorksheetFunction.Transpose(templateSheet.Range("A11:A15").Value), vbCrLf)
    ' 読み込んだテンプレートヘッダーのプレースホルダを実際の値に置換
    templateHeader = Replace(templateHeader, "__TITLE__", dataSheet.Range("B1").Value)
    templateHeader = Replace(templateHeader, "__MAIN_HEADING__", dataSheet.Range("B2").Value)

    ' --- 3. 動的コンテンツの生成 ---
    Dim bodyContent As String
    bodyContent = GenerateHtmlElement(dataSheet.Range("B4").Value, "p")
    bodyContent = bodyContent & RangeToHtmlTable(dataSheet.Range("B6:D10"))

    ' --- 4. 全てのHTMLパーツを結合 ---
    finalHtml = templateHeader & bodyContent & templateFooter
    
    ' --- 5. UTF-8でHTMLファイルとして書き出し ---
    Set streamObj = CreateObject("ADODB.Stream")
    With streamObj
        .Type = 2
        .Charset = "UTF-8"
        .Open
        .WriteText finalHtml
        .SaveToFile outputFilePath, 2
        .Close
    End With

    Set streamObj = Nothing
    MsgBox "HTMLレポートの生成が完了しました。"
End Sub

---

コードのポイント解説

① ワークシートからテンプレートを読み込むテクニック

templateHeader = Join(WorksheetFunction.Transpose(templateSheet.Range("A1:A10").Value), vbCrLf)

この一行は、ワークシートのA列に並んだ複数のセルを、改行を含む一つの長い文字列に変換するための非常に効率的なテクニックです。

1. .Range(...).Valueで、指定範囲の値が配列として読み込まれます。
2. WorksheetFunction.Transposeで、その縦長の配列が横長の配列に転置されます。
3. Join関数が、横長の配列（1次元配列）の各要素を、第二引数で指定した文字（ここでは改行コードvbCrLf）で連結し、一つの文字列を返します。 セルを一つずつループして&で連結するよりも、はるかに高速に処理が完了します。

② プレースホルダの置換 (Replace関数)

templateHeader = Replace(templateHeader, "__TITLE__", dataSheet.Range("B1").Value)

読み込んだテンプレート文字列に対して、Replace関数を使い、__TITLE__のような目印（プレースホルダ）を、データシートから取得した実際の値に置換していきます。

③ 動的コンテンツの挿入

finalHtml = templateHeader & bodyContent & templateFooter

この手法の最も強力な点は、静的なテンプレートと動的なコンテンツを自由に組み合わせられることです。

1. まず、プレースホルダを置換したテンプレートのヘッダー部分を用意します。
2. 次に、RangeToHtmlTableなどの関数を呼び出して、レポートの本体となるコンテンツ（表など）を動的に生成します。
3. 最後に、テンプレートのフッター部分とすべてを&で連結し、最終的なHTMLを完成させます。

---

まとめ

HTMLの**「静的なレイアウト」と「動的なデータ」を分離する**テンプレート方式は、メンテナンス性と再利用性に優れた、非常に高度なプログラミングの考え方です。

• HTMLの専門家は、VBAコードに触れることなくTemplateSheet上でデザインを修正できる。
• VBA開発者は、HTMLの細かなデザインを気にすることなく、データ処理のロジックに集中できる。

このように役割分担も可能になるため、複雑なHTMLレポートを自動生成する際には、ぜひこのテンプレート方式の導入を検討してみてください。