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

tx12345

Board Regular
Joined
Aug 28, 2006
Messages
165
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,911
Office Version
  1. 365
Platform
  1. Windows
tx

Any chance of an example URL?
 

tx12345

Board Regular
Joined
Aug 28, 2006
Messages
165
Hi Norie

sure:

Link with sample data

I came up with a workable solution for the button clicking:
Code:
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-known Member
Joined
Apr 28, 2004
Messages
75,911
Office Version
  1. 365
Platform
  1. Windows
Well here's code that will extract all the tables from a webpage.

Is it similar to the other code?
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

Board Regular
Joined
Aug 28, 2006
Messages
165

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,911
Office Version
  1. 365
Platform
  1. Windows
I found this link useful.
 

tx12345

Board Regular
Joined
Aug 28, 2006
Messages
165

ADVERTISEMENT

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?

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,911
Office Version
  1. 365
Platform
  1. Windows
You probably want to stick in another loop to wait until the page is ready.
 

tx12345

Board Regular
Joined
Aug 28, 2006
Messages
165
i stuck an application.wait in there for a few seconds. seems to work.

thanks again

tx
 

Watch MrExcel Video

Forum statistics

Threads
1,112,794
Messages
5,542,543
Members
410,560
Latest member
mergim
Top