Getting inner text from HTML options selector using VBA

Question

I am trying to get the product title and prices for each type of packaging from the combo box of the web page (http://www.havanahouse.co.uk/product/rattrays-marlin-flake-pipe-tobacco-50g).

<h1 itemprop="name" class="product_title entry-title">Rattray&#8217;s Marlin Flake Pipe Tobacco 50g tin</h1>

<div itemprop="offers" itemscope itemtype="http://schema.org/Offer">


  <table class="variations" cellspacing="0">
    <tbody>
      <tr>
        <td class="label">
          <label for="pa_packages">Packing</label>
        </td>
        <td class="value">
          <select id="pa_packages" class="" name="attribute_pa_packages" data-attribute_name="attribute_pa_packages">
            <option value="">Choose an option</option>
            <option value="5-x-50g-tins">5 x 50g Tins</option>
            <option value="50g-tin">50g Tin</option>
          </select><a class="reset_variations" href="#">Clear selection</a> 
        </td>
      </tr>
    </tbody>
  </table>



  <p class="price"><span class="amount">&pound;12.94</span>&ndash;<span class="amount">&pound;63.95</span>
  </p>

Each price is only for 1 options. So for example the price for a 50g-tin is 12.94 and 5-x-50g-tins is 63.95.

These are all different packaging options for the same product. I need to bring data in the following table:

Product name, packaging option, price.

any insight how to do it?

VBA? Running where? You mean you are screen scraping? stackoverflow.com/questions/27066963/… — mplungjan
– mplungjan, Commented Aug 21, 2016 at 16:27
You can use IE automation to do this: google for "VBA automate IE" - plenty of examples out there (and here on SO) — Tim Williams
– Tim Williams, Commented Aug 21, 2016 at 17:21

Ryan Wildry · Accepted Answer · 2016-08-21 23:40:12Z

1

Here is a sample that will load the page to the URL specified, wait for it to load, then show the InnerText and Value properties of each of the elements.

Public Sub IE_Test()
    Dim IE       As Object: Set IE = CreateObject("InternetExplorer.Application")
    Dim Elements As Object
    Dim Element  As Object

    With IE
        'Show and navigate to page
        .Visible = True
        .Navigate ("http://www.havanahouse.co.uk/product/rattrays-marlin-flake-pipe-tobacco-50g")

        'Wait until page loads
        Do While .busy And .readystate <> 4
            DoEvents
        Loop

        'Create a collection of Option Tags, which are part of pa_packages
        Set Elements = .document.getelementbyID("pa_packages").getelementsbyTagName("option")

        'Show the element's properties
        For Each Element In Elements
            Debug.Print "The InnerText is: " & Element.innertext
            Debug.Print "The Value is: " & Element.Value
        Next
    End With
End Sub

answered Aug 21, 2016 at 23:40

Ryan Wildry

5,6971 gold badge17 silver badges36 bronze badges

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

1 Comment

Suren Grigoryan Over a year ago

This is Great Ryan, many thanks. one more problem - the issue is that each option has its price and I am struggling to bringing in the relevant price for the selected option to my spreadsheet. the price change depends on the options and the prices are in the HTML code below: £12.94–£63.95

Collectives™ on Stack Overflow

Getting inner text from HTML options selector 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