Web scrape

[SQUIDD]

Hi

I am so bad at this kind of thing

how can i open this web page in excel, change the page size at the bottom to 200 and reload the page ready to copy and paste.

Racecard


Cheers

dave

 

[Ombir]

I didn't find 200 in the drop down list of page Size. Maximum is 100.

 

[SQUIDD]

Hi Ombir

Oh yes. Sorry.
Then 100 it is then.
Thanks for looking

Dave

 

[Ombir]

Try this:

Sub webscraping()
Dim i       As Long
Dim tbl     As Variant
Dim doc     As HTMLDocument
Dim ie      As InternetExplorer
Dim table   As HTMLTable

Set ie = New InternetExplorer

With ie
    .Visible = True
    .Navigate "http://www.gbgb.org.uk/RaceCard.aspx?dogName=Raparee%20Holly"
End With

Do While ie.ReadyState <> 4: DoEvents: Loop

Set doc = ie.Document
Set table = doc.getElementById("ctl00_ctl00_mainContent_cmscontent_DogRaceCard_lvDogRaceCard_ctl00")

With table
    ReDim tbl(0 To .Rows.Length - 2, 0 To .Rows(1).Cells.Length)
        For x = 0 To UBound(tbl, 1) - 1
            For y = 0 To UBound(tbl, 2) - 1
                tbl(x, y) = .Rows(x).Cells(y).innerText
            Next
        Next
End With
Range("A1").Resize(x, y) = tbl
ie.Quit
End Sub

It will download the data from table on website into your sheet directly. If formatting of some columns get messed up then change the number format of respective columns to TEXT before running the code.

Before running the code you also need to set reference to Microsoft HTML object library and Microsoft Internet Controls from VBA References.

 

[SQUIDD]

Hi Ombir

Thankyou so much, i have been wanting to to this little project for months.

I get an compile error at this part of the code

Dim doc As HTMLDocument

user - defined type not defined

Any idea what this may be.

thanks

Dave

 

[SQUIDD]

Hi Ombir

I commented this part out to continue and the compile errors continued down to

Set ie = New InternetExplorer

thanks

Dave

 

[SQUIDD]

sorry ombir

i missed the last part of your wording at the bottom as i read it on my phone.

sorry mate, will try again.

Dave

 

[SQUIDD]

Hi ombir

Thanks mate, youve gone the extral mile getting the data into my sheet for me, such a neat way of doing it.
Im really impressed.

I do have a small probelm, for some reason, mine is only copying the data 19 lines of data and a header row.

Did your code grab the whole lot for you?

thanks again

Dave

 

[Ombir]

There is some structure problem with this table. I have modified it and added functionality to Select page size of 100 from combo Box.

Sub Ombir_04Dec2016()
Dim i       As Long
Dim tbl     As Variant
Dim doc     As HTMLDocument
Dim ie      As InternetExplorer
Dim table   As HTMLTable
Dim pinput  As HTMLInputTextElement

Set ie = New InternetExplorer

With ie
    .Visible = True
    .Navigate "http://www.gbgb.org.uk/RaceCard.aspx?dogName=Raparee%20Holly"
    Do While ie.ReadyState <> 4: DoEvents: Loop
End With

Set doc = ie.Document
Set pinput = doc.getElementById("ctl00_ctl00_mainContent_cmscontent_DogRaceCard_lvDogRaceCard_ctl00_ctl03_ctl01_PageSizeComboBox_Input")
pinput.Focus
Application.SendKeys "100{RETURN}"
Application.Wait Now() + TimeValue("00:00:02")

Do While ie.Busy: DoEvents: Loop
Set table = doc.getElementById("ctl00_ctl00_mainContent_cmscontent_DogRaceCard_lvDogRaceCard_ctl00")

With table
    ReDim tbl(0 To .Rows.Length - 1, 0 To .Rows(1).Cells.Length)
        For x = 0 To UBound(tbl, 1)
            For y = 0 To UBound(tbl, 2) - 1
                On Error Resume Next
                tbl(x, y) = .Rows(x).Cells(y).innerText
            Next
        Next
End With
Range("A1").Resize(x, y) = tbl
ie.Quit
End Sub

Regards,
Ombir

 

[SQUIDD]

Oh WOW

You are a genius.
I cannot thank you enough.
Worked absolutly perfectly.
the wait for 2 seconds, would this possible not get data if the website was running slow?

I have another page i need to get info from, but im keen to look at your code and see if i can replicate it.
I have experimented with inspecting elements of the page to try and find what part to ammend.

If i get stumped, can i PM you?

many, many thanks ombir.

Dave