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-13 07:18:24Z

2

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.

edited Aug 13, 2015 at 7:18

answered Aug 13, 2015 at 5:33

R.Katnaan

2,5364 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

mdian Over a year ago

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 Over a year ago

upss.. sorry, for error "user-defined type is not define" its is solve, using 'Microsoft scripting runtime' link, but now comes up new error "runtime error '91 : Object variable or with block variable not set"

R.Katnaan Over a year ago

which line give you error. I already tested it, it work well for me.

mdian Over a year ago

debugger pointing to this line "templateText = templateStream.ReadAll", iam new in using excel macros, maybe i miss some configuration for my Ms Excel.

R.Katnaan Over a year ago

If you add reference, there is no way to shown error. I suggest to restart your excel application and re-run it.

|

QHarr · Accepted Answer · 2018-06-30 06:00:26Z

1

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:

answered Jun 30, 2018 at 6:00

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Collectives™ on Stack Overflow

How to insert Excel cell in to HTML class with VBA

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related