Searching websites for specific strings and importing.

[crazyeyeschase]

To begin I am rebuilding a vehicle and am currently working on sourcing parts from multiple vendors. I am not sure excel can perform what I want but I was once deep in the weeds with macros and enjoyed it and figured maybe I can use excel to speed this process of part/price sourcing.

What I am wanting to do is to have excel search a website for a part number and then input the price (at minimum) into a cell.

From what I have read and can tell the import from web will not work unless the vendor lists the info into a table which I could not figure out.

I think using xmlhttp might be an option but even then, from my little refresher today I think this only searches the current page and cant perform a search of the website.

Technically I need to search the entire website for the product, find the product page, then search the source code for the pricing or any other strings I need.

Obviously each vendor will have a different source code and the macro would need to be customizable to each site if this is even possible.

Can anyone point me in the right direction or if this is even possible?

I am still searching for myself but though I should get some professional feedback here.

 

[crazyeyeschase]

Not that I have much yet but a few of the websites look similar with the HTTML search function and this will work good enough if i can then search and import from the source code.

Now this only searches for the data in cell A1 and ill need to adjust the range for the entire column but this works and finds what i need.

Now to figure out how to search the source code and import.

Sub website_search()

Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate ("Klempf's British Parts. Search" & Range("A4")) 'it will search for value, what is in column A1
IE.Visible = True

End Sub

 

[diddi]

it is possible to get the exact info out but each website will have different coding to get the data you need. that said you could search an entire list of parts and get all info on the site very rapidly if they have a master list of parts which most do

 

[crazyeyeschase]

it is possible to get the exact info out but each website will have different coding to get the data you need. that said you could search an entire list of parts and get all info on the site very rapidly if they have a master list of parts which most do

Correct, Each website will need its own macro which shouldn't be a big deal once i figure out the best way to search the source code.

I am slowly working on this myself, i suppose the question should be.

What is the best way to code in VBA to search the source code. For a string then import that into a cell.

Here is the source code from one website

<span class="price">$8.50 Each</span>

This is on line 620 so once i can get it to search for the correct link i can get it to find this code and might need to make another code to remove everything other than the price

If a search result is empty that line 620 looks different which means its a simple if/then statement

 

[crazyeyeschase]

Alright so I have managed to do some digging and no this isn't what I am wanting but its getting closer.

Here is the code

Sub Sample()
Dim ie As Object
Dim retStr As String

Set ie = CreateObject("internetexplorer.application")
Set Rng = Range("a1")

With ie
.Navigate ("website" & Rng)
.Visible = True
End With

Do While ie.readystate <> 4: Wait 5: Loop

DoEvents

retStr = ie.document.getElementsByClassName("price")

Dim filesize As Integer
Dim FlName As String

FlName = "D:\chase\Sample.Txt"

filesize = FreeFile()

Open FlName For Output As #filesize

Print #filesize, retStr
Close #filesize
End Sub

Private Sub Wait(ByVal nSec As Long)
nSec = nSec + Timer
While nSec > Timer
DoEvents
Wend
End Sub

This is the source code on the website

<div class="add-info">
<div class="prices">
<div class="product-price">
<span class="price">$8.50 Each</span>
</div>
</div>
<div class="buttons">

When I run this code my text file looks like this

[object HTMLSpanElement]

 

[crazyeyeschase]

Alright I have done more playing and see where i messed up.

Now this will input the data into a cell

Sub Sample()
Dim ie As Object
Dim price As String

Set ie = CreateObject("internetexplorer.application")
Set Rng = Range("a1")

With ie
.Navigate ("WEBSITE" & Rng)

.Visible = False
End With

Do While ie.readystate <> 4: Wait 5: Loop

DoEvents

price = ie.document.getElementsByClassName("price")(0).innertext

Cells(1, "B").Value = price

End Sub

Private Sub Wait(ByVal nSec As Long)
nSec = nSec + Timer
While nSec > Timer
DoEvents
Wend
End Sub

Now when I have time I need it to loop for the entire column of A

 

[diddi]

whats the url

 

[crazyeyeschase]

whats the url

Each website is different but I am currently working on klempfs.

Klempf's British Parts

Vintage British Motorcycle Parts

www.klempfsbritishparts.com

Using their search function you get a link like this

Klempf's British Parts. Search

Vintage British Motorcycle Parts

www.klempfsbritishparts.com

The 70-3300 is the part number which is the reference in excel.

In this example the search comes back with multiple results so the code is only seeking the first interaction of what I'm looking for.