CODEWITHSUNDEEP
vbaexcelinternet-explorer
David G
David G

Reputation: 2357

Navigate to blank page in IE with Excel

I have a piece of VBA code in Excel that takes cells where there is plain HTML text written, takes that HTML to IE, and pastes it back as formatted text without HTML tags in the cell. I picked the code here: HTML Text with tags to formatted text in an Excel cell

Contrary to that question I linked, I have to do this for a LOT of cells, so I want to speed up a little bit. Since CreateObject("InternetExplorer.Application") takes a long time to process I am trying to use the same object several times, but it fails on the line .Navigate "about:blank" the second time it comes to it.

Here is the code:

Sub ConvertHTMLrecu()
    Dim Ie As Object
    Dim rng As Range, rng2 As Range
    Dim i As Integer
    Dim iChr As Integer

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

    For i = 1 To 40
        Set rng = Feuil3.Range("A" & i)
        Set rng2 = Feuil3.Range("C" & i)

        With Ie
            .Visible = False

            .Navigate "about:blank"

            .Document.body.InnerHTML = rng.Value

            .ExecWB 17, 0
            .ExecWB 12, 2

            ActiveSheet.Paste Destination:=rng2

            .Quit
        End With
    Next i

End Sub

What I need

I would like one of two things. Either find a way to navigate to a blank page again so I can run the macro a second time without having to reset the object OR find a better way to format HTML text in Excel (since this is all this macro is doing).

Upvotes: 0

Views: 1738

Answers (1)

ManishChristian
ManishChristian

Reputation: 3774

You are quitting Ie object in first loop and trying to access same in second loop, hence getting error.

Just remove .quit from loop and place below lines in the last line just before End Sub: 

Ie.quit
Set Ie = Nothing

Upvotes: 2

Related Questions