Show HTML code as "HTML preview" within an excel cells

Question

Hello,

I have a row with cells containing text strings with HTML code inside. I would like to see just HTML preview (no code) within cells. I would like to still have HTML code inside the cell, but it apears as text without <> etc...

Is that possible?

I tried

Sub Sample2()

    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

I get stuck with this line .document.body.createtextrange.execCommand "Copy"

could someone help me?

Answer 1

I get an error on the same line with IE 10.  Apparently it used to work with IE 9 but something changed.  Rather than try to find a fix, I found another example at:

Format HTML in Excel

Hope this helps,

Eric

P.S. - The code that seemed to work is:

Sub test()

    Dim objData As DataObject ' Set a reference to MS Forms 2.0

    Dim sHTML As String

    Dim sSelAdd As String

    Dim rng As Range

    Cells(1, 1).Value = Cells(11, 4).Value ' HTML text in Cell "D11" for my example

    Set rng = ActiveSheet.Cells(1, 1)

    Set objData = New DataObject

    sHTML = rng.Text

    objData.SetText sHTML

    objData.PutInClipboard

    rng.Select

    ActiveSheet.PasteSpecial Format:="Unicode Text"

End Sub

Answer 2

I get stuck with this line .document.body.createtextrange.execCommand "Copy"

The code should a little different:

  a) Use createtextrange only once to get an object

  b) Call execCommand "SelectAll" before "Copy"

That works, but there is an issue, you can see it when you make the IE visible:

When the "Copy" is called, a security window in the IE pops up that asks you to enable the access to the IE. When you don't allow the access, the code always fails.

Andreas.

Answer 3

thank you for your hints, but i am still unable to get macro working..

i can see it in IE but than cannot get it from there :/  and i didnt noticed pop up to enable the access after visible.true 

Sub Sample()

    Dim Ie As Object

    Set Ie = CreateObject("InternetExplorer.Application")

    With Ie

        .Visible = True

        .Navigate "about:blank"

        .document.body.InnerHTML = Sheets("Sheet1").Range("A1").Value

.document.body.createtextrange.execCommand "SelectAll"

.document.body.execCommand "Copy"

ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("A1")

        .Quit

    End With

End Sub

Bolded part is still the problem ..

Answer 4

Sub Test()

  Dim Ie As Object 'SHDocVw.InternetExplorer

  Dim B As Object 'MSHTML.HTMLBody

  Dim T As Object 'MSHTML.IHTMLTxtRange

  Set Ie = CreateObject("InternetExplorer.Application")

  With Ie

    .Visible = True

    .Navigate "about:blank"

    .Document.body.innerHTML = Range("A1")

    Set B = .Document.body

    Set T = B.createTextRange

    T.execCommand "SelectAll"

    T.execCommand "Copy"

    ActiveSheet.Paste Range("A2")

    .Quit

  End With

End Sub

Answer 5

I used your code, but still not get the result.

macro stopped at the T.execCommand "SelectAll"

>>Object doesnt support this property or method.

the same as before, it will open IE but can not get it from there.