HTML Text with tags to formatted text in an Excel cell

vba excel html-parsing
Kevin McGovern picture Kevin McGovern · Apr 3, 2012 · Viewed 195.1k times · Source

Is there a way to take HTML and import it to excel so that it is formatted as rich text (preferably by using VBA)? Basically, when I paste to an Excel cell, I'm looking to turn this:

<html><p>This is a test. Will this text be <b>bold</b> or <i>italic</i></p></html>

into this:

This is a test. Will this text be bold or italic

Answer

Siddharth Rout picture Siddharth Rout · Apr 3, 2012

Yes it is possible :) In fact let Internet Explorer do the dirty work for you ;)

TRIED AND TESTED

MY ASSUMPTIONS

  1. I am assuming that the html text is in Cell A1 of Sheet1. You can also use a variable instead.
  2. If you have a column full of html values, then simply put the below code in a loop

CODE (See NOTE at the end)

Sub Sample()
    Dim Ie As Object
    
    Set Ie = CreateObject("InternetExplorer.Application")
    
    With Ie
        .Visible = False
        
        .Navigate "about:blank"
        
        .document.body.InnerHTML = Sheets("Sheet1").Range("A1").Value
        
        .document.body.createtextrange.execCommand "Copy"
        ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("A1")
        
        .Quit
    End With
End Sub

SNAPSHOT

enter image description here

NOTE: Thanks to @tiQu answer below. The above code will work with new IE if you replace .document.body.createtextrange.execCommand "Copy" with .ExecWB 17, 0: .ExecWB 12, 2 as suggested by him.

Related questions

Excel VBA to open the first search result page of google

I have to open the google search page using excel Macro. I am able to successfully open the google search page, after I give my search parameters in excel. However, my task is to open the first returned search result …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More