Displaying HTML in Excel cell

Recently I was tasked with coming up with an idea of how to display HTML tags inside an Excel sheet which were in the rows of 1 column. It had to be done automatically with a click of a button (i.e. conversion of each HTML cell into properly formatted cell that displays HTML content with applied styling) and of course VBA had to play a role in it. (Full disclosure: Idea borrowed from here)

Well, after a lot of trials & errors, here is the general idea & code: For each cell in the column we first open Internet Explorer and navigate to “about:blank” (blank page). Then, we ‘copy’ the cell’s content to such blank page and ‘select all & copy’ the content into the clipboard. From there, we read it again and paste it into the same cell from which we have first copied it.

It might sound fairly complicated because……it is. Plus, one clear disadvantage is that if, during time the macro is executed, anything is copied to clipboard by the user, it will 1000% lead to inconsistent results. And this is not even to talk about speed.

Warning: The code below can take up to 4 minutes with 250 rows to execute. This is intentional because if there is no “Wait()” then for some reason it again leads to inconsistent results (e.g. where the same row will be copied up to 4 times -> 4 rows with same HTML content)

Before you will be able to execute this VBA macro, read this website on how to load “Microsoft Forms 2.0 Object Library”.

Other sources which have been used
https://stackoverflow.com/questions/9999713/html-text-with-tags-to-formatted-text-in-an-excel-cell
https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba
http://wordmvp.com/FAQs/MacrosVBA/ManipulateClipboard.htm
http://excel-macro.tutorialhorizon.com/vba-excel-reference-libraries-in-excel-workbook/
https://stackoverflow.com/questions/34203056/excel-vba-fails-to-load-internet-explorer


Sub DisplayHTMLContentProperly()
Dim rng As Range
Dim row As Range
Dim cell As Range
Dim Ie As Object
Set Ie = CreateObject("InternetExplorer.Application")
Ie.Visible = True 'to be tested
Dim DataObj As MSForms.DataObject ' for clipboard
Set DataObj = New MSForms.DataObject

Dim sht As Worksheet
Dim LastRow As Long
Set sht = ThisWorkbook.Worksheets("SheetName")
LastRow = sht.ListObjects("DataName").Range.Rows.Count

Set rng = Range("L2:L" & LastRow)

For Each row In rng.Rows
For Each cell In row.Cells

Application.CutCopyMode = False
DataObj.GetFromClipboard
myString = " "
DataObj.SetText " "

If Not IsEmpty(cell) Then
With Ie
.Visible = True
.Navigate "about:blank"
While .Busy Or .ReadyState 4: DoEvents: Wend
.Document.body.InnerHTML = cell 'update to the cell that contains HTML you want converted

.ExecWB 17, 0 'Select all contents in browser
.ExecWB 12, 2 'Copy them

'get data from clipboard (due to copy method above) and paste it into the cell
DataObj.GetFromClipboard
myString = DataObj.GetText(1)
'MsgBox myString - to debug
cell = myString

'delete anything from the clipboard
DataObj.Clear
Application.CutCopyMode = False
DataObj.SetText " "

End With
End If
'Do Something
Set HTML = Nothing
Application.Wait (Now + 0.000000011574 * 1200)

Next cell
Next row

Ie.Quit
Set Ie = Nothing
Application.CutCopyMode = False

MsgBox "I am now done with proper formatting of HTML column."

'move to see just summary page
Sheets("Summary").Activate

End Sub

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s