how to convert a column of text with html tags to formatted text in vba in excel

Question

I would like to know how to use VBA script to covert a whole column of cells with html tags to formatted text (based on those tags).

screenshot of spreadsheet

I was able to convert ONE cell based on a previous listing: HTML Text with tags to formatted text in an Excel cell

Using the following:

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
             'update to the cell that contains HTML you want converted
        .ExecWB 17, 0
             'Select all contents in browser
        .ExecWB 12, 2
             'Copy them
        ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("B1")
             'update to cell you want converted HTML pasted in
        .Quit
    End With
End Sub

But this only converts the first cell in the column. (In the example above, I manually typed in A2 and B2 to do the second cell as well). I'm sorry if this is a naive question, but I'm new to VBA. I've tried to use loops and play with ranges, but unsuccessfully.

Answer 1

Your code is working just for the first line because you are getting and setting only the first line :

'get the A1 cell value
.document.body.InnerHTML = Sheets("Sheet1").Range("A1").Value
 'set the B1 cell value
 ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("B1")

To apply your code for all the lines you have to execute it inside a loop.

So your code becomes :

Sub Sample()

Dim Ie As Object

'get the last row filled
lastRow = Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Row
'loop to apply the code for all the lines filled
For Row = 1 To lastRow
    Set Ie = CreateObject("InternetExplorer.Application")
    With Ie
        .Visible = False
        .Navigate "about:blank"
        .document.body.InnerHTML = Sheets("Sheet1").Range("A" & Row).Value
             'update to the cell that contains HTML you want converted
        .ExecWB 17, 0
             'Select all contents in browser
        .ExecWB 12, 2
             'Copy them
        ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("B" & Row)
             'update to cell you want converted HTML pasted in
        .Quit
    End With
    Set Ie = Nothing
Next

End Sub

Answer 2

Please Check:

Option Explicit


    Sub Sample()

        Dim Ie As Object

        Dim i As Long, lastrow As Long

        lastrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row

        On Error Resume Next

       For i = 1 To lastrow
         Set Ie = CreateObject("InternetExplorer.Application")

        With Ie

            .document.body.InnerHTML.Reset
            .Visible = False
            .Navigate "about:blank"
            .document.body.InnerHTML = Sheets("Sheet1").Cells(i, "A").Value
                 'update to the cell that contains HTML you want converted


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


            Sheets("Sheet1").Paste Destination:=Sheets("Sheet1").Cells(i, "B")


                 'update to cell you want converted HTML pasted in


            .Quit

        End With

          Next

    End Sub