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.
Bookmarks