Paste/parse HTML into excel

randys106 used Ask the Experts™
I hate when what should be simple is complicated.  

I want to paste the HTML from the body of a website into my spreadsheet.  When I go to the website, view the source, copy it, and paste it to a cell, the HTML is parsed like I need it (every line in a different cell).  But from VBA when I paste all the HTML is in one cell.  How do I fix this?

Here is my code and spreadsheet.  Note that there are two sheets (one like VBA gives it to me, one like I want it to be).

Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate ""

Do Until IE.ReadyState = 4: DoEvents: Loop

Sheets(1).Range("A1").Value = IE.document.Body.innerHTML

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Use this routine to paste the data as you desire:

Public Sub PasteHTML(ByVal Target As Range, ByVal HTML As String)

    Dim HTMLLines As Variant
    HTMLLines = Split(HTML, vbCrLf)
    Target.Resize(UBound(HTMLLines) - 1).Value = Application.Transpose(HTMLLines)

End Sub

Top Expert 2008

Call it this way from your code:

   PasteHTML Sheets(1).Range("A1"), IE.document.Body.innerHTML



zorvek, your the man.  Thanks for the quick response.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial