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? http://stackoverflow.com/questions/27066963/scraping-data-from-website-using-vba — mplungjan, Aug 21 '16 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, Aug 21 '16 at 17:21

score 1 · Accepted Answer · answered Aug 21 '16 at 23:40

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

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 — Suren Grigoryan, Aug 23 '16 at 08:20

Getting inner text from HTML options selector using VBA

1 Answers1