Use VBA to pull price information from website.

[Tclem]

I have had the repetitive task of updating tire prices on an excel spreadsheet from tirerackwholesale.com. It involves copying the manufacturer part number from Cells.(B2:B418), pasting into the website "Part number Search," then copying the "price" to enter back into Cells.(D2:D418). It gets old very quickly. Therefore, I have been trying to code in VBA in order to automate this process for me when I need to update.

My first approach was to hyperlink each part number, and then use the source of that website page and parse it for the price. I was able to get the VBA to find the price for the first part number with the following code:




Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells=Range("B2").Cells Then
Dim IE As New InternetExplorer
IEVisible=False
IE.navigate "http://www.tirerackwholesale.com/tires/TireSearchResults.jsp?Vnum=" &Range("B2").Value

Do
DoEvents
Loop Until IE.readyState=READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc As HTMLDocument
Set Doc=IE.document
Dim sdd As String
sdd= Trim(Doc.getElementsByTagName("strong") (8).innerText)
Range ("D2").Value=sdd


End If

End Sub






There might be an easier way to code what I already have. I am very new to VBA and am learning as I go. Any help would be greatly appreciated! If this code is the best one for this action, I somehow need to add on a loop to it in order to continue the action through the entire list of 418 "part numbers."

Thanks in advance

 

[pbornemeier]

Post 3 in this thread shows a nifty method:
http://www.mrexcel.com/forum/showthread.php?649000
This string
yfs_l10_zgq12.cbt"">"
is what appears in the View Source window for the target page, immediately before the desired data.

I can't test it for your site since it requires an account.