Searching websites for specific strings and importing.

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88
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.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88
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

Well-known Member
Joined
May 20, 2004
Messages
3,273
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
May 6, 2014
Messages
88
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

Board Regular
Joined
May 6, 2014
Messages
88

ADVERTISEMENT

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

Board Regular
Joined
May 6, 2014
Messages
88
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

Well-known Member
Joined
May 20, 2004
Messages
3,273
Office Version
  1. 2010
Platform
  1. Windows
whats the url
 

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88
whats the url

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


Using their search function you get a link like this


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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,612
Messages
5,625,846
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top