1

First, I am very new with VBA and have only got as far as I am with solutions from other questions asked online. What I have is a macro that opens IE to a specified URL, enters text into a search, loads the results, then loops the search through more specific search values.

What I am trying to do is scrape the results of a search into excel. However, the results don't appear in the resulting HTML code but look to be generated by a script on the website.

An example of the page I am searching: https://www.gamestop.com/PickUpAtStore/75083/0/917850

When loaded the results are found on the page, but not in the page source. Looking at the page source there looks to be a script that pulls the results in:

<script id="stores" type="text/x-handlebars-template">
{{#if this}}
<ul>
    {{#each this}}
    <li id="{{StoreNumber}}|{{#if true}}917850 {{/if}}" class="{{#if false}}checkOnly{{/if}}"">

        <div class="fluidWrapper ats-storelist" id="{{StoreNumber}}">
            <div class="contactInfo">
                <div class="title ats-storetitle">{{DisplayName}}</div>
                <div class="address ats-storeaddress">
                    {{{AddressStreet}}}<br />{{AddressCityStateZip}}
                </div>
                <div class="phoneNumber ats-storephone">
                    {{Phone}}
                </div>
            </div>
            <div class="rightInfo">
                <div class="distance ats-storedistance">{{Distance}} {{#if true}}<i id="showHoldOptions_{{StoreNumber}}" class="{{#if false}} plus_{{/if}}icon"></i>{{/if}}</div>
            </div>
        </div>
..................

Ideally, what I would like to happen is when the results are loaded the store name, address and phone # are put into excel starting at A4, B4, C4 and adding each store to the next line.

Am I looking in the entirely wrong place to grab these results? I appreciate any help solving this.

edit adding current macro:

Sub Search_Cell()

Dim ie As Object
Dim lRow As Long
Dim URL As Range

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True

For Each URL In Range("B1")
ie.navigate URL.Value
Application.StatusBar = "Submitting"
While ie.Busy
    DoEvents
Wend
Next

For lRow = 1 To 89

With ie.document
.all("puas_search").Value = Sheets("Zipcodes").Range("A" & lRow).Value
.getElementById("puas_search").Focus
End With

Application.SendKeys ("~")
Application.Wait Now + #12:00:02 AM# 'wait 2 seconds

   ' Get results of search

   ' Add Store name to A4, Address to B4, Phone# to C4 (but for following searches start at the next empty row)

   ' Add following results to next row


Next lRow 'loop to next search


ie.Quit
Set ie = Nothing

MsgBox "Done"

End Sub
Balmy
  • 11
  • 3
  • What does your existing code look like right now? I [answered a very similar question recently](https://stackoverflow.com/a/47040459/8535397). Using the IE object, it *should* be able to read the generated html (not caring about the source). – Tigregalis Oct 31 '17 at 17:36
  • @Tigregalis With the html scrapping, the OP will only retrieve `{AddressStreet}` and he wants the value given by it and not {AddressStreet}. I think It is not possible to get this data with only html scrapping. – danieltakeshi Oct 31 '17 at 17:39
  • 1
    @danieltakeshi it's not HTML scraping, it's DOM traversal. I said "generated HTML" but what I really meant was the DOM. The IE object lets us traverse the DOM - hell, if we wanted to, we could use it to automate form submissions. – Tigregalis Oct 31 '17 at 17:40
  • @Tigregalis I add my current macro, it 'works' but results are captured manually. I had tried other solutions to parse and grab the div tags but didn't end up working. Unfortunately, I didn't save the failed code section. – Balmy Oct 31 '17 at 18:07

1 Answers1

0

I solved this, I was entirely wrong thinking that the results couldn't be scraped from the html. Thank You @Tigregalis for nudging me in the right direction.

Here is the snippet of code that pulls the data I need, places it in the correct location in excel, then moves the to next row.

    Set HTMLDoc = IE.document

    Set Stores = HTMLDoc.getElementsByClassName("contactInfo")

    For Each Store In Stores
        ColNum = 1

        For Each Name In Store.Children

            Cells(RowNum, ColNum) = Name.innerText
            ColNum = ColNum + 1

        Next Name
        RowNum = RowNum + 1

    Next Store
Balmy
  • 11
  • 3