CODEWITHSUNDEEP
vbaexcelextract
Merlyn Koh
Merlyn Koh

Reputation: 11

How to scrape data from website using VBA?

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

Upvotes: 0

Views: 1584

Answers (1)

Matthew Martin
Matthew Martin

Reputation: 131

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.

Upvotes: 1

Related Questions