PDA

View Full Version : web extraction



dvnr_76
07-27-2014, 01:25 AM
I want to download all my students results from a website into excel. in the website when we enter the hall ticket number of a student and then click the submit button the result will be displayed. I want to import all my students data into excel automatically.

westconn1
07-27-2014, 02:21 PM
what have you got so far?

westconn1
07-28-2014, 04:48 AM
i had a play with this, it was pretty easy to get a result, but as you have given no details as to how your information is set up, hard to know what to do with the results

what have you got so far?

westconn1
07-28-2014, 02:35 PM
as i have no idea where your list of hall tickets comes from, i have just put a sequence of numbers from 11A21A0502 To 11A21A0507
the results are inserted into a worksheet from cell A3


Set wb = CreateObject("internetexplorer.application")
wb.navigate2 "xxxxxx.htm"
Do Until wb.readystate = 4: DoEvents: Loop
Dim a() As String
ReDim a(1 To 4, 1 To 1000)
rw = 1
col = 1
Set doc = wb.document
For i = 502 To 507
doc.all("htno").Value = "11A21A0" & i
doc.forms(0).all("babtn").Click
Set div = doc.getelementbyid("subhtm")
Do
Set tdiv = div.all(5)
Loop While tdiv Is Nothing
Do Until div.all(5).innertext = "11A21A0" & i: Loop
Set t = div.getelementsbytagname("table")(1)
a(2, rw) = div.all(5).innertext
For Each tr In t.getelementsbytagname("tr")
For Each td In tr.getelementsbytagname("td")
a(col, rw) = td.innertext
col = col + 1

Next
col = 1
rw = rw + 1
Next
rw = rw + 1

Next
ReDim Preserve a(1 To 4, 1 To rw - 1)
Range("a3").Resize(rw - 1, 4) = WorksheetFunction.Transpose(a)
wb.Visible = True
wb.Quitif the results are likely to exceed 1000 rows, then increase the dimension of a from 1000 to exceed any likely number of rows, this is tested and appears to work correctly