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

SAS code for replacing missing values with 0 in a folder full of datasets

%macro loopOverDatasets();
/*imho good practice to declare macro variables of a macro locally*/
%local datasetCount datasetName iter inMember;

/*get number of datasets + name of datasets*/
proc sql noprint ;
select count(*)
into: datasetCount
from dictionary.tables
where libname = “EXAM”;
quit;

/*initiate loop*/
%let iter=1;
%do %while (&iter.<= &datasetCount.);

proc sql noprint;
select memname
into: datasetName
from dictionary.tables
where libname = “EXAM” and monotonic() eq &iter.;
quit;

%put &iter &datasetCount &datasetName &inMember;
/*now you can apply your logic to the dataset*/
data &datasetName.;
set exam.&datasetName.;
array change _numeric_;
do over change;
if change=. then change=0;
end;
run;

%let iter=%eval(&iter.+1);
%end;
%mend;

%loopOverDatasets;

2015 in review

The WordPress.com stats helper monkeys prepared a 2015 annual report for this blog.

Here’s an excerpt:

A San Francisco cable car holds 60 people. This blog was viewed about 590 times in 2015. If it were a cable car, it would take about 10 trips to carry that many people.

Click here to see the complete report.