How to scrape data from website using VBA?

Question

I'm a newbie at VBA and was trying to extract flight prices from Expedia (SG to Bangkok) for some practice. My code is not working out too well, unfortunately. It only returned one price (which I have no idea where it came from). Would really appreciate it if anyone could help me out. Thank you!

Sub ExtractRaw() 
Dim wb As Workbook 
Dim ws As Worksheet 
Set wb = ThisWorkbook 
Set ws = wb.Sheets("Sheet1") 

Dim ie As Object 
Set ie = CreateObject("InternetExplorer.Application") 
With ie 
.Visible = True .navigate "https://www.expedia.com.sg/Flights-Search?rfrr=TG.LP.SrchWzd.Flight&langid=2057&trip=OneWay&leg1=from:Singapore,%20Singapore%20(SIN-Changi),to:Bangkok,%20Thailand%20(BKK-Suvarnabhumi%20Intl.),departure:" & DateAdd("d", 1, Date) & "TANYT&passengers=children:0,adults:1,seniors:0,infantinlap:Y&options=cabinclass:economy,sortby:price,carrier:&mode=search&paandi=true" 

Do While ie.Busy 
DoEvents 
Loop 

Dim doc As HTMLDocument 
Set doc = ie.document 
While ie.readyState <> 4 
Wend 
On Error Resume Next 

Dim i As Integer For i = 0 To 200 
Range("A1").Value = ie.document.getElementById("flight-listing-container").getElementsByClassName("dollars price-emphasis")(i).innerText 

Next i 
ie.Quit 
Application.EnableEvents = True 
End With 
End Sub

See the answer in this post: stackoverflow.com/questions/44955755/… — Kostas K.
– Kostas K., Commented Oct 25, 2017 at 16:43

Matthew Martin · Accepted Answer · 2017-10-25 16:06:04Z

1

Your issue lies here I think:

Dim i As Integer 
    For i = 0 To 200 
    Range("A1").Value = ie.document.getElementById("flight-listing-container").getElementsByClassName("dollars price-emphasis")(i).innerText 
Next i

You're looping an arbitrary 200 times and then repeatedly updating cell A1 with the price. This means you'll always be left with the inner text of the last element that matched in A1

Try

Range("A1").Offset(i,0).Value = ie.document.getElementById("flight-listing-container").getElementsByClassName("dollars price-emphasis")(i).innerText

That'll give you a list of all inner text it finds on that elment down the A column until your loop terminates.

Really, I think you should be determining how many iterations you'll need to loop before diving into the loop, unless you've got a good reason to loop 200 times each time.

edited Oct 25, 2017 at 16:06

answered Oct 25, 2017 at 15:58

Matthew Martin

1315 bronze badges

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

1 Comment

Merlyn Koh Over a year ago

ah yes now it returns multiple values! Thank you. But the values returned seem to be skipping rows... It would be blank from cell A1-7 and somewhere in the middle as well

Collectives™ on Stack Overflow

How to scrape data from website using VBA?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related