Downloading web content using form

[paul_taylor]

I need to download web content into a spreadsheet. I'm using a form and the web browser control. (The reason is that I have to pass variables to get to the page I need--can't use Excel web query).

This is the last piece of code I'm using after I load the document.

Sheets(1).Cells(1, 1) = doc.DocumentElement.innerText

It's downloading all the content, but it's all going into cell A1. What I'd like it to do is populate down to row 100 1,000 or however far it has to go with one line of text per row. The page I'm downloading is thousands of rows long, hence this method cuts off everything below the amount of text that can fit in one cell.

 

[Norie]

Well here's some code for extracting every table on a page to an Excel worksheet.

I've not included a URL and it's not perfect but it might give you an idea of what I meant in my earlier post.

All you need to pass to the sub is the document object of the page.

Option Explicit
 
Sub GetAllTables(doc As Object)
Dim ws As Worksheet
Dim rng As Range
Dim tbl As Object
Dim rw As Object
Dim cl As Object
Dim tabno As Long
Dim nextrow As Long
Dim I As Long
 
    Set ws = Worksheets.Add
 
    For Each tbl In doc.getElementsByTagName("TABLE")
 
        tabno = tabno + 1
 
        nextrow = nextrow + 1
 
        Set rng = ws.Range("B" & nextrow)
 
        rng.Offset(, -1) = "Table " & tabno
 
        For Each rw In tbl.Rows
 
            For Each cl In rw.Cells

                rng.Value = cl.outerText

                Set rng = rng.Offset(, 1)

                I = I + 1

            Next cl
 
            nextrow = nextrow + 1
 
            Set rng = rng.Offset(1, -I)

            I = 0

        Next rw
 
    Next tbl
 
    ws.Cells.ClearFormats
 
End Sub

I think you could call it from your code like this, though I can't be sure without seeing the rest of the code.

GetAllTables doc