Word can open HTML files, which contain the code of webpages. Word automatically converts the HTML content and displays it as a Word document. You can use this feature to import HTML content into your Excel workbook.
Consider the following example HTML code:
<!DOCTYPE html>
<html>
<body>
<p>Line 1</p>
<p>Line 2</p>
<p>Line 3</p>
</body>
</html>
This HTML file, named page.htm, displays three paragraphs as shown in Figure when opened in a browser.
Using the following VBA code, the contents of these three paragraphs are extracted and placed into three cells in Excel . The code trims the paragraph end character using string functions Left() and Len().
Sub ReadWordHtml()
Dim appWord As Word.Application
Dim document As Word.document
Dim paragraphText As String
Dim i As Integer
ThisWorkbook.Worksheets("Sheet1").Activate
' Create Word application object
Set appWord = CreateObject("Word.Application")
' Open the HTML file as a Word document
Set document = appWord.Documents.Add(ThisWorkbook.Path & "C:\Users\POPOLY\Desktop\page.html")
' Loop through all paragraphs
For i = 1 To document.Paragraphs.Count
paragraphText = document.Paragraphs(i).Range.Text
' Remove the paragraph end character and write to Excel
Cells(i, 8).Value = Left(paragraphText, Len(paragraphText) - 1)
Next i
' Close document and quit Word
document.Close
appWord.Quit
Set document = Nothing
Set appWord = Nothing
End Sub
Explanation:
- The HTML file is opened as a Word document via Documents.Add().
- Word parses the HTML and treats the <p> tags as paragraphs.
- Each paragraph’s text includes a paragraph end character, which is removed by trimming the last character.
- The cleaned paragraph text is transferred to column 8 (column H) in the Excel worksheet.