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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
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
76,303
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
76,303
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
76,303
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
 

Forum statistics

Threads
1,141,427
Messages
5,706,372
Members
421,445
Latest member
blueman0110

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top