+ Reply to Thread
Results 1 to 1 of 1

Web data pull, row by row, paste outputS in row. Need to loop

  1. #1
    Registered User
    Join Date
    01-28-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    1

    Angry Web data pull, row by row, paste outputS in row. Need to loop

    Hi,

    So long story short, I'm putting all my sports cards in an excel file. Column A is the value that gets pasted into Mavin.io. My current VBA code pulls the info I'm wanting but only for ONE row and it pastes the data in vertically and I need it horizontally to keep the data on the same row.


    I have about 20,000 to 40,000 cards I need to do this for.


    Here is the Code
    'start a new subroutine called SearchBot
    Sub SearchBot()

    'dimension (declare or set aside memory for) our variables
    Dim objIE As InternetExplorer 'special object variable representing the IE browser
    Dim aEle As HTMLLinkElement 'special object variable for an <a> (link) element
    Dim y As Integer 'integer variable we'll use as a counter
    Dim result As String 'string variable that will hold our result link



    Application.ScreenUpdating = False

    'initiating a new instance of Internet Explorer and asigning it to objIE
    Set objIE = New InternetExplorer

    'make IE browser visible (False would allow IE to run in the background)
    objIE.Visible = True

    'navigate IE to this web page (a pretty neat search engine really)
    objIE.navigate "websiteaddress/" (had to change to submit)

    'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    'start of loop


    'in the search box put cell "A2" value, the word "in"
    objIE.document.getElementById("search-input").Value = _
    Sheets("Data").Range("A2").Value

    'click the 'go' button
    objIE.document.getElementById("search-button").Click

    'wait again for the browser
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop


    'the first search result will go in row 2
    y = 2

    'for each <a> element in the collection of objects with class of 'result__a'...
    For Each aEle In objIE.document.getElementsByClassName("estimate-box")

    '...get the href link and print it to the sheet in col C, row y
    'result = aEle
    'Sheets("Data").Range("N" & y).Value = result

    '...get the text within the element and print it to the sheet in col D
    Sheets("Data").Range("O" & y).Value = aEle.innerText
    Debug.Print aEle.innerText
    y = y + 1

    'is it a yellowpages link?
    If InStr(result, "yellowpages.com") > 0 Or InStr(result, "yp.com") > 0 Then
    'make the result red
    Sheets("Data").Range("N" & y).Interior.ColorIndex = 3
    'place a 1 to the left
    Sheets("Data").Range("N" & y).Value = 1
    End If

    'increment our row counter, so the next result goes below
    y = y + 1

    'repeat times the # of ele's we have in the collection
    Next

    'add up the yellowpages listings
    'Sheets("Data").Range("B1").Value = _
    'Application.WorksheetFunction.Sum(Sheets("data").Range("B2:B100"))

    'end loop

    'close the browser
    objIE.Quit
    Application.ScreenUpdating = True
    'exit our SearchBot subroutine
    End Sub



    I have attached my workbook as well if this makes it easier for anyone willing to help.


    Thank you in advance!

    Note I'm just good enough at VBA to really really get myself into trouble so if you see any code written wrong thats why.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Loop up and pull a list of data from 1 cell
    By bordemkills in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-07-2020, 02:14 PM
  2. vba code to pull data from all pages in loop from webpage.
    By xlhelp7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-08-2017, 06:59 AM
  3. Pull the data from different closed workbooks paste into master vba
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-12-2015, 06:36 PM
  4. How to write VBA, so I can refresh input cell and capture all outputs from each loop
    By willaisme in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-10-2014, 04:15 PM
  5. [SOLVED] to pull data from one sheet and paste specific on another.
    By lengwer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-20-2014, 11:21 AM
  6. Web query loop that outputs data to selected column downwards
    By Kakatumba in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-18-2013, 06:11 AM
  7. Pull data using loop in another workbook
    By codesRus in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-17-2012, 03:47 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1