CODEWITHSUNDEEP
htmlvbaexcel
mdian
mdian

Reputation: 33

How to insert Excel cell in to HTML class with VBA

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.

Upvotes: 2

Views: 2498

Answers (2)

QHarr
QHarr

Reputation: 84465

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:

HTML output

Upvotes: 1

R.Katnaan
R.Katnaan

Reputation: 2526

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.

Upvotes: 2

Related Questions