Get table data from web then 'click' button for next page

[tx12345]

Hi

a while back i dug up an excellent thread on the subject but lost it, and it took forever to find it. so i'll ask this way

the xl web query works if you have a specific web page but lots of times there'll be an .asp sort of page where the web page name doesn't change so you have to go to the web page, and then click on the button to get the next page and so on.

i know how to get to the web page with VBA, but some ideas on how to direct VBA to a specific table on the page, place the contents in xl, and then how to 'click' the 'next' button, especially if it is one of those javascript buttons buried in an ocean of html code. i need to know exactly what in the code to look for to tell vba to 'click it' to move the program along

many thanks as always



tx

 

[Norie]

tx

Any chance of an example URL?

 

[tx12345]

Hi Norie

sure:

Link with sample data

I came up with a workable solution for the button clicking:

SendKeys "{TAB 2}", True
SendKeys "%{ENTER}", True

the table extraction business is the trick right now. in the actual web page the link does not change in the address bar, but the sendkeys code will get me to the next page. once i am there i see a table similar to the one at the sample link, with a similar name and the exact same layout.

the link i lost had a nice way to identify the table by name, then copy and paste it to the worksheet. it was only a few lines of code, but i forgot what it was

tx

Any chance of an example URL?

 

[Norie]

Well here's code that will extract all the tables from a webpage.

Is it similar to the other code?

Sub Test()
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .navigate "http://equitytoincome.com/ex.htm"
        Do Until .readyState = 4: DoEvents: Loop
        Set doc = ie.document
        GetAllTables doc
    End With
End Sub

Sub GetAllTables(d)
    For Each e In d.all
        If e.nodename = "TABLE" Then
            Set t = e
        
            tabno = tabno + 1
            nextrow = nextrow + 1
            Set rng = Range("B" & nextrow)
            rng.Offset(, -1) = "Table " & tabno
            For Each r In t.Rows
                For Each c In r.Cells
                    rng.Value = c.innerText
                    Set rng = rng.Offset(, 1)
                    I = I + 1
                Next c
                nextrow = nextrow + 1
                Set rng = rng.Offset(1, -I)
                I = 0
            Next r
        End If
    Next e
End Sub

By the way you really should try to avoid using SendKeys.

 

[tx12345]

yes the code looks similar, thanks. i'll have to see how it works. is there a way to specify which table instead of getting them all?

as far as send keys, i agree, but they are a quick fix. if there is some sort of tutorial on how one can code a 'click' on a specific web page link, in all the various ways links are set up (esp asp/php paes usig javascript, showing links like "javascript:__doPostBack('0$MainContent$P','2')")

i am definitely open for some ideas

thanks again

 

[Norie]

I found this link useful.

 

[tx12345]

The trick here is to get the data in the table from the current web page. the send keys gets me there, but when i apply the code instead of getting the data on the second page i just get the data from the first.

any thoughts?

Sub Test()
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .navigate "http://equitytoincome.com/ex.htm"
        Do Until .readyState = 4: DoEvents: Loop

SendKeys "{TAB 2}", True
SendKeys "{ENTER}", True

Set doc = ie.document
GetAllTables doc

    End With
End Sub

The info in the worksheet will always be the same regardless of where the set doc code is placed. the idea is to move from one page to the next, get the data on the page, add it to xl, move to the next, add it, etc. the current code seems only to get the data on the very first page, and is overwritten when a new set of data comes in


thanks again

 

[Norie]

You probably want to stick in another loop to wait until the page is ready.

 

[tx12345]

i stuck an application.wait in there for a few seconds. seems to work.

thanks again

tx

 

[Legacy 98055]

Hi TX. Here is my attempt to get the data from a specific table on a webpage. Disregard my first reply in this post. The example still works for the most part.

http://www.mrexcel.com/board2/viewtopic.php?t=201772&highlight=table