How to insert Excel cell in to HTML class with VBA

Question

Is it possible to insert Excel cell value into HTML format with VBA?

I have HTML template:

<html>
<head><title></title></head>
<body>
<div class="article">
   <div class="title"></div>
   <div class="date"></div>
   <div class="content"></div>
</div>
</body>
</html>

and data in Excel :

|  Title   |  Date   |  Content                             |
+----------+---------+--------------------------------------+
| Sample1  |20150811 | Lorem ipsum dolor                    |
| Sample2  |20150812 | Lorem ipsum dolor                    |
| Sample3  |20150813 | Lorem ipsum dolor                    |

Goal: I want to create an Excel macro to insert cell into HTML class, and save each line into HTML file, so the result is 3 HTML with same template and different content.

Is anyone can help me pointing some reference? or clue, what should I do to achieve the goal. I try to search but the result is scraping from web to Excel. Nothing from Excel to HTML.

R.Katnaan · Accepted Answer · 2015-08-13T07:18:24.913

Here, I got one for you:

Public Sub exportHTML()

    Dim templateStream As TextStream
    Dim templatePath, templateText, newFile, newText As String
    Dim dataSheet As Worksheet
    Dim row As Integer

    'Create FileSystemObject
    Dim fsObject As New FileSystemObject

    'Set template file path
    templatePath = "C:\template.html"

    'Set sheet
    Set dataSheet = Sheets("sheetname")

    'If template file is exist.
    If fsObject.FileExists(templatePath) Then

        'Open template file
        Set templateStream = fsObject.OpenTextFile(templatePath)

        'Read data
        templateText = templateStream.ReadAll

        'Close template file
        templateStream.Close

        'Looping all row
        For row = 2 To 4 'Here you need to modify the end row as you like

            'Get new html file (filename: Range("A").html)(e.g Sample1.html)
            'You can change file name.
            newFile = ThisWorkbook.Path & "\" & dataSheet.Range("A" & row) & ".html"

            'Set old text to new text
            newText = templateText

            'Set title
            newText = Replace(newText, "<div class=""title""></div>", "<div class=""title"">" & dataSheet.Range("A" & row) & "</div>")

            'Set date
            newText = Replace(newText, "<div class=""date""></div>", "<div class=""date"">" & dataSheet.Range("B" & row) & "</div>")

            'Set content
            newText = Replace(newText, "<div class=""content""></div>", "<div class=""content"">" & dataSheet.Range("C" & row) & "</div>")

            'Create new HTML file and open
            Open newFile For Output As #1

                'Write file content
                Print #1, newText

            'Close new file
            Close

        Next row

    Else
        Call MsgBox("Template HTML file is not exist.", vbExclamation, "Exporting HTML")
    End If

End Sub

I tested with the following data:

    +----------+---------+--------------------------------------+
    |    A     |    B    |   C                                  |   
+---+----------+---------+--------------------------------------+
| 1 |  Title   |  Date   |  Content                             |
+---+----------+---------+--------------------------------------+
| 2 | Sample1  |20150811 | Lorem ipsum dolor                    |
| 3 | Sample2  |20150812 | Lorem ipsum dolor                    |
| 4 | Sample3  |20150813 | Lorem ipsum dolor                    |
+---+----------+---------+--------------------------------------+

I got the three output Sample1.html, Sample2.html, Sample3.html in the same directory with excel file with the desired content.

hi.. Nicolas thanks for your answer, when i try error window comes up and say "user-defined type not define" and ponting to "Dim templateStream As TextStream" is there any library to support textStream or what? — mdian, Aug 13 '15 at 05:56
upss.. sorry, for error "user-defined type is not define" its is solve, using 'Microsoft scripting runtime' [link](http://stackoverflow.com/questions/3233203/how-do-i-use-filesystemobject-in-vba), but now comes up new error "runtime error '91 : Object variable or with block variable not set" — mdian, Aug 13 '15 at 06:24
which line give you error. I already tested it, it work well for me. — R.Katnaan, Aug 13 '15 at 06:40
debugger pointing to this line "templateText = templateStream.ReadAll", iam new in using excel macros, maybe i miss some configuration for my Ms Excel. — mdian, Aug 13 '15 at 06:43
If you add reference, there is no way to shown error. I suggest to restart your excel application and re-run it. — R.Katnaan, Aug 13 '15 at 06:48
Try as `Set templateText = templateStream.ReadAll`, If shown error, remove `Set`. — R.Katnaan, Aug 13 '15 at 06:53
i already try restart my excel and Set templateText = templateStream.ReadAll, error is shown and i remove it, and now still stuck in error "templateText = templateStream.ReadAll", seem like the macro can't read the template. any solution for it? — mdian, Aug 13 '15 at 07:09
I modify the code, actuallly this line `Set templateStream = fsObject.OpenTextFile(templatePath)`. Try again, it will be OK. Sorry for that. — R.Katnaan, Aug 13 '15 at 07:19

score 1 · Answer 2 · answered Jun 30 '18 at 06:00

You can use CSS selectors to update the elements within an HTML document as shown below:

Option Explicit
Public Sub AddHMTLInfo()
    Dim htmlText As String, html As HTMLDocument '<== Requires reference to HTML Object Library
    htmlText = [A1]                              '<==Reading you sample in from cell
    If htmlText = vbNullString Then Exit Sub
    Set html = New HTMLDocument
    With html
        .body.innerHTML = htmlText
        .querySelector(".title").innerText = "myTitle"
        .querySelector(".date").innerText = "myDate"
        .querySelector(".content").innerText = "myContent"
        Debug.Print .body.innerHTML                   '<== Verify changes
    End With
End Sub

Output:

How to insert Excel cell in to HTML class with VBA

2 Answers2