VBA scraping with Excel

Question

I am trying to scrape data from: http://www.boliga.dk/salg/resultater?so=1&sort=omregnings_dato-d&maxsaledate=today&iPostnr=&gade=&type=Villa&minsaledate=2017

IN connection with this I have two questions.

Michał Perłakowski has giving an excellent guide how to scrape but the code he is using is getElementById(Scraping data from website using vba). Since the webpage I want to scrape from does not use an ID. I am wondering what alternatives available. My guess would be getElementsByClassName.

My next question is how to make the macro change page (I have more than 100) can I just write "next"?

Dim appIE As Object
Set appIE = CreateObject("internetexplorer.application")

With appIE
    .navigate "http://www.boliga.dk/salg/resultater?so=1&sort=omregnings_dato-d&maxsaledate=today&iPostnr=&gade=&type=Villa&minsaledate=2017"
    .Visible = True
End With

Do While appIE.Busy
    DoEvents
Loop

Set allRowOfData = appIE.document.getElementsByName("Bygget")

Dim myValue As String: myValue = allRowOfData.Cells(7).innerHTML

appIE.Quit
Set appIE = Nothing

Range("A1").Value = myValue

Codes are now attached

thomasuponor
– thomasuponor

2018-02-22 11:52:34 +00:00
Commented Feb 22, 2018 at 11:52 — thomasuponor
– thomasuponor, Commented Feb 22, 2018 at 11:52
And what is the problem you are having with the above?

QHarr
– QHarr

2018-02-22 11:58:24 +00:00
Commented Feb 22, 2018 at 11:58 — QHarr
– QHarr, Commented Feb 22, 2018 at 11:58
The macro does not return any value to excel

thomasuponor
– thomasuponor

2018-02-22 12:00:24 +00:00
Commented Feb 22, 2018 at 12:00 — thomasuponor
– thomasuponor, Commented Feb 22, 2018 at 12:00
I would like to return all the values in "Bygget".

thomasuponor
– thomasuponor

2018-02-22 12:06:05 +00:00
Commented Feb 22, 2018 at 12:06 — thomasuponor
– thomasuponor, Commented Feb 22, 2018 at 12:06
Everything in "Bygget"

thomasuponor
– thomasuponor

2018-02-22 12:07:56 +00:00
Commented Feb 22, 2018 at 12:07 — thomasuponor
– thomasuponor, Commented Feb 22, 2018 at 12:07

QHarr · Accepted Answer · 2018-02-22 12:33:19Z

1

Try this:

Option Explicit

Sub scrape()

    Dim appIE As Object
    Dim ihtml As Object

    Set appIE = CreateObject("internetexplorer.application")

    With appIE

        .Visible = True
        .navigate "http://www.boliga.dk/salg/resultater?so=1&sort=omregnings_dato-d&maxsaledate=today&iPostnr=&gade=&type=Villa&minsaledate=2017"

        While .Busy = True Or .readyState < 4: DoEvents: Wend

        Set ihtml = .document


        Dim allRowOfData As Object

        Set allRowOfData = appIE.document.getElementById("searchresult")

        Dim r As Long, c As Long

        Dim curHTMLRow As Object


        For r = 1 To allRowOfData.Rows.Length - 1

            Set curHTMLRow = allRowOfData.Rows(r)

            For c = 0 To curHTMLRow.Cells.Length - 1 'comment out
                Cells(r + 1, c + 1) = curHTMLRow.Cells(c).innerText    '  Cells(r + 1, c + 1) = curHTMLRow.Cells(7).innerText
            Next c 'comment out

        Next r

        .Quit

    End With

    Set appIE = Nothing

End Sub

Just the column of interest rather than whole table:

Option Explicit

Sub scrape()

    Dim appIE As Object
    Dim ihtml As Object

    Set appIE = CreateObject("internetexplorer.application")

    With appIE

        .Visible = True
        .navigate "http://www.boliga.dk/salg/resultater?so=1&sort=omregnings_dato-d&maxsaledate=today&iPostnr=&gade=&type=Villa&minsaledate=2017"

        While .Busy = True Or .readyState < 4: DoEvents: Wend

        Set ihtml = .document


        Dim allRowOfData As Object

        Set allRowOfData = appIE.document.getElementById("searchresult")

        Dim r As Long, c As Long

        Dim curHTMLRow As Object


        For r = 1 To allRowOfData.Rows.Length - 1

            Set curHTMLRow = allRowOfData.Rows(r)
            Cells(r + 1, c + 1) = curHTMLRow.Cells(7).innerText

        Next r

        .Quit

    End With

    Set appIE = Nothing


End Sub

Reference:

https://www.experts-exchange.com/questions/28571716/Excel-VBA-WEb-Data-Scraping-from-a-Table.html

edited Feb 22, 2018 at 12:33

answered Feb 22, 2018 at 12:21

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

thomasuponor Over a year ago

That allowed me to return 1935

thomasuponor Over a year ago

Tried to ran it a few times. It returns 1,3, or 4 results.

QHarr Over a year ago

I have now allowed for all table to be returned or if you comment out the two lines mentioned and swop in Cells(r + 1, c + 1) = curHTMLRow.Cells(7).innerText instead of Cells(r + 1, c + 1) = curHTMLRow.Cells(c).innerText then you will have just the column of interest

thomasuponor Over a year ago

Now it returns all the data which is alright. But it does not change the page

QHarr Over a year ago

I think it would be good to post how to make the macro change page as a new question and give an example of what you want. Are you expecting to enter a value somewhere and then refresh the page?

|

Collectives™ on Stack Overflow

VBA scraping with Excel

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related