VBA: Web Scraping HTML Code

R

rwcg2d

New Member
Joined
Jul 11, 2016
Messages
43
I am trying to pull the following HTML information from the following webpage (and would hope to duplicate this effort to pull all of the prices on the page):


https://www.ebay.com/sch/i.html?_from=R40&_nkw=ralph+lauren&_sacat=1059&LH_TitleDesc=0&_dmd=1&rt=nc


How would I do this? I have the following code already, and would like to pull the highlighted information (Price & Listing Title) in the screenshots below

Code: 
[FONT='inherit']Sub eBayPull()
Set objIE = CreateObject("InternetExplorer.Application")
WebSite = "https://www.ebay.com/sch/i.html_from=R40&_nkw=ralph+lauren&_sacat=1059&LH_TitleDesc=0&_dmd=1&rt=nc"With objIE
.Visible = True        
.navigate WebSite        
Do While .Busy Or .READYSTATE <> 4            
DoEvents        
Loop    
End With    
End Sub[/FONT]

rp4fecw6jq421.png

jd7gg9hajq421.png
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Hi, try the below.

I'm not sure what libraries are essential, it's been a while.

Here are the ones in the book I tested it in:

Microsoft HTML Object Library
JavaWebStart 1.0 Type Library
Microsoft Internet Controls
Microsoft XML v6.0

*Also couldn't see any screenshots, so pulling description and price

Code: 
Public IE As New SHDocVw.InternetExplorer


Sub PullEBay()


    Dim HTMLdoc As MSHTml.HTMLDocument
    Dim othwb As Variant
    Dim objShellWindows As New SHDocVw.ShellWindows


    Set IE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}")
    
        With IE
            .Visible = True
            .Navigate "https://www.ebay.com/sch/i.html?_nkw=i.html_from=R40&_nkw=ralph+lauren&_sacat=1059&LH_TitleDesc=0&_dmd=1&rt=nc"
            While .Busy Or .ReadyState <> 4: DoEvents: Wend




                Set HTMLdoc = IE.Document
                ProcessHTMLPage HTMLdoc
                
            .Quit
        End With




End Sub


Sub ProcessHTMLPage(HTMLPage As MSHTml.HTMLDocument)


    Dim HTMLItem As MSHTml.IHTMLElement
    Dim HTMLItems As MSHTml.IHTMLElementCollection
    Dim rownum As Long
    
    rownum = 1


    Set HTMLItems = HTMLPage.getElementsByClassName("s-item__title")
    
    For Each HTMLItem In HTMLItems


            Cells(rownum, 1).Value = HTMLItem.innerText
            rownum = rownum + 1
        
    Next HTMLItem
    
    rownum = 1
    
    Set HTMLItems = HTMLPage.getElementsByClassName("s-item__price")
    
    For Each HTMLItem In HTMLItems


            Cells(rownum, 2).Value = HTMLItem.innerText
            rownum = rownum + 1
        
    Next HTMLItem




End Sub
 
Last edited:
Upvote 0
Copy Pasted the code, added the References but ran into this error:

Automation Error
Unspecified Error
Debugged at this line: While .Busy Or .ReadyState <> 4
 
Upvote 0
Strange, I tried the URL and all works fine.

Maybe turn visible to TRUE, and comment out the .Quit. Then you can look through the pages code at the "s-item__title" and "s-item__price" items and see what is happening vs. what you are seeing.

[TABLE="width: 654"]
<tbody>[TR]
[TD]
SPONSORED

Brooks Brothers 16 1/2 6/7 Blue Oxford Shirt[/TD]
[TD="align: right"]$39.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Shirt Cotton Button Down Stripe Long Sleeve, Size L[/TD]
[TD="align: right"]$13.24[/TD]
[/TR]
[TR]
[TD]BROOKS BROTHERS 346 SHORT SLEEVE Salmon COTTON PIQUE POLO SHIRT MENS MEDIUM[/TD]
[TD="align: right"]$1.30[/TD]
[/TR]
[TR]
[TD]Brooks Brothers White Polo[/TD]
[TD="align: right"]$3.59[/TD]
[/TR]
[TR]
[TD]
SPONSORED

MEN'S Brooks Brothers Med LONG SLEEVE SHIRT[/TD]
[TD="align: right"]$39.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers - Golden Fleece - Men's L Large Short Sleeve Polo Shirt - Brown[/TD]
[TD="align: right"]$21.99[/TD]
[/TR]
[TR]
[TD]mens brooks brothers dress shirt Medium Slim Fit Non Iron[/TD]
[TD="align: right"]$20.00[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Mens "Original Polo" Oxford Dress Shirt Size 14.5-32 Made in USA[/TD]
[TD="align: right"]$18.39[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Regent Men's Non-Iron Gingham Supima Dress Shirt - $92 NWT[/TD]
[TD="align: right"]$39.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Shirt Mens Medium Button Down Sport Shirt Houndstooth Check[/TD]
[TD="align: right"]$24.12[/TD]
[/TR]
[TR]
[TD]Brooks Brothers 346 16 - 34 Mens Slim Fit Shirt Dress Long Sleeve Button Striped[/TD]
[TD="align: right"]$35.35[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Madison Men's Non-Iron Oxford Supima Dress Shirt Size 17.5-33[/TD]
[TD="align: right"]$22.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Multi-Color Plaid Oxford Sport Shirt Size Medium[/TD]
[TD="align: right"]$18.39[/TD]
[/TR]
[TR]
[TD]Brooks Brothers 1818 Mens Shirt 18 34/35 Dress Long Sleeve Button Front Striped[/TD]
[TD="align: right"]$33.33[/TD]
[/TR]
[TR]
[TD]
SPONSORED

Men’s Brooks Brothers 16 33 Shirt[/TD]
[TD="align: right"]$49.99[/TD]
[/TR]
[TR]
[TD]
SPONSORED

Brooks Brothers Med Men's Long Sleeve[/TD]
[TD="align: right"]$49.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's 100% Linen Multi-Color Striped Sport Shirt Size XL[/TD]
[TD="align: right"]$18.39[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Regent Non-Iron Supima Dress Shirt Size 16-35 - $92 NWT[/TD]
[TD="align: right"]$31.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Regent Men's Non-Iron Supima Dress Shirt Size 15.5-35 - $92 NWT[/TD]
[TD="align: right"]$39.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers 346 Men's Non Iron S/S Button Down Green & Blue Plaid Shirt - M[/TD]
[TD="align: right"]$12.70[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Madison Non-Iron Oxford Supima Dress Shirt Size 16-34[/TD]
[TD="align: right"]$18.39[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Red Fleece Men's Oxford Dress Shirt Size Large - $99 NWT[/TD]
[TD="align: right"]$23.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Shirt Long Sleeve Classic Fit Pink White Striped Mens 14 1/2-33[/TD]
[TD="align: right"]$17.85[/TD]
[/TR]
[TR]
[TD]
SPONSORED

Men's XL Brooks Brothers Stripped Button Down shirt[/TD]
[TD="align: right"]$49.99[/TD]
[/TR]
[TR]
[TD]
SPONSORED

Men’s 15 32 Pink Brooks Brothers Shirt[/TD]
[TD="align: right"]$39.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers 346 Non-Iron Men's Long Sleeve Button Front Shirt Size 18 4-5[/TD]
[TD="align: right"]$19.97[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's 100% Irish Linen Sport Shirt Size Small Extra Slim Fit[/TD]
[TD="align: right"]$18.39[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Est 1818 Performance Polo Men's Short Sleeve Shirt Size XL Blue[/TD]
[TD="align: right"]$16.15[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Regent Non-Iron Supima Dress Shirt Size 16-34 - $92 NWT[/TD]
[TD="align: right"]$31.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's L/S Button Down Yellow & Blue Checkered Shirt - 16.5-5[/TD]
[TD="align: right"]$10.50[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Slim Fit Non-Iron Mens Long Sleeve Button-Down Shirt Sz 14.5 2/3[/TD]
[TD="align: right"]$23.60[/TD]
[/TR]
[TR]
[TD]Brooks Brothers 346 Men's Non Iron L/S Button Up Blue Plaid Shirt - 16.5-6/7[/TD]
[TD="align: right"]$16.15[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's All Cotton S/S Button Down Shirt - USA Made - No Tag/Med??[/TD]
[TD="align: right"]$10.50[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Multi-Color Plaid Oxford Sport Shirt Size Large[/TD]
[TD="align: right"]$15.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's L/S Button Down Red & White Striped Dress Shirt - 17 1/2-3[/TD]
[TD="align: right"]$13.05[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Mens Size Medium Long Sleeve Button Collared Shirt Check -SD24[/TD]
[TD="align: right"]$14.35[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Regent Casual Dress Shirt Size XL - $60 NWT[/TD]
[TD="align: right"]$23.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Non-Iron Check Oxford Dress Shirt Size Large[/TD]
[TD="align: right"]$18.39[/TD]
[/TR]
[TR]
[TD]
SPONSORED

Brooks Brothers Med Long Sleeve Shirt[/TD]
[TD="align: right"]$59.99[/TD]
[/TR]
[TR]
[TD]
SPONSORED

Brooks Brothers Men's Large Blue Button Down Shirt[/TD]
[TD="align: right"]$49.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Non-Iron Multi-Color Plaid Supima Dress Shirt Size 15.5-33[/TD]
[TD="align: right"]$18.39[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Multi-Color Plaid Oxford Supima Dress Shirt Size Small[/TD]
[TD="align: right"]$15.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's 100% Cotton L/S Button Down Blue Checkered Shirt - 17-4[/TD]
[TD="align: right"]$16.95[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Est. 1818 Non-Iron Men's Long Sleeve Button-Front Shirt Sz 16-34[/TD]
[TD="align: right"]$19.27[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's L/S Button Down Orange Pin Striped Dress Shirt - Size 17-3[/TD]
[TD="align: right"]$12.65[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Non-Iron Striped Twill Dress Shirt Size 16-34/35[/TD]
[TD="align: right"]$18.39[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Non-Iron Traditional Fit Dress Shirt Size 16.5-33[/TD]
[TD="align: right"]$18.39[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Mens Size 16.5/36 Long Sleeve Button Collared Shirt Check -HS82[/TD]
[TD="align: right"]$17.93[/TD]
[/TR]
[TR]
[TD]BROOKS BROTHERS BUTTON DOWN DRESS SHIRT Sz Mens 16 - 3 Blue[/TD]
[TD="align: right"]$8.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Est. 1818 Men's Long Sleeve Button-Front Shirt Size 17-35 Stripe[/TD]
[TD="align: right"]$17.22[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's SLIM FIT "Original Polo" Oxford Sport Shirt Size XL[/TD]
[TD="align: right"]$15.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Non-Iron Supima Twill Dress Shirt Size 16.5-32[/TD]
[TD="align: right"]$24.99[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's L/S Button Down Light Olive Green Dress Shirt - Size 17-5[/TD]
[TD="align: right"]$10.45[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men's Elliot Advantage Khaki Chino Pants - Tag 38x34/Fits 38x33[/TD]
[TD="align: right"]$18.00[/TD]
[/TR]
[TR]
[TD]Brooks Brothers Men Size XL Extra Slim Fit Long Sleeve Button Collar Shirt -HS79[/TD]
[TD="align: right"]$17.93[/TD]
[/TR]
[TR]
[TD]Brooks Brothers 1818 Performance Polo Mens Short Sleeve Shirt Sz XL Original Fit[/TD]
[TD="align: right"]$16.15[/TD]
[/TR]
[TR]
[TD]
SPONSORED

Men’s Brooks Brothers 16 33 extra slim fit shirt[/TD]
[TD="align: right"]$39.99[/TD]
[/TR]
[TR]
[TD]
SPONSORED

Brooks Brothers 16 1/2 MEN'S Shirt[/TD]
[TD="align: right"]$99.99[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Ran a few more tests.

Looks like there is an issue when there is a buy it now, and auction price for one item. Not sure how to handle that sorry.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Locked
  • Question Question
VBA ebay Scrapping
Replies
7
Views
847
RoryA
RoryA
C
  • Question Question
Scraping a value held in a <button>
Replies
0
Views
862
carolinetoynbee
C
J
  • Question Question
E-Bay scrape of completed items sold for a specified search
Replies
1
Views
2K
JAnders
J
E
  • Question Question
Object variable or With block not set error when trying to fill in input fields on a website
Replies
0
Views
1K
EnginerdUNH
E
S
  • Question Question
VBA how to stop a For Each loop
Replies
3
Views
751
Simmo81
S

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top