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