VBA to pull product information

Softsurf105

New Member
Joined
Dec 18, 2018
Messages
14
Hi,

I have a Manufacturer # that I search for on a website and need to pull the; Manufacturer name, part number and Description. The website used to search is https://www.mscdirect.com/


Using the number 63713408 as an example, I have to search for 63713408 in the search bar and then copy over the: Manufacurer: Cleveland, MFr Part #: [COLOR=rgba(0, 0, 0, 0.87)]C12204 and description: [/COLOR][COLOR=rgba(0, 0, 0, 0.87)][h=2]5/8", 2MT, 118° Point, High Speed Steel Taper Shank Drill Bit[/h][/COLOR]
[COLOR=rgba(0, 0, 0, 0.87)][h=3]Oxide Finish, 4-7/8" Flute Length, 8-3/4" OAL, Spiral Flute, Series 2410[/h][/COLOR]


Please reach out with any help or guidance. I have over 6,000 items to search.

Thanks in advance!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Softsurf105

New Member
Joined
Dec 18, 2018
Messages
14
Sorry not sure why the information got changed but please see below for a more clear explanation.

I have a Manufacturer # that I search for on a website and need to pull the; Manufacturer name, part number and Description. The website used to search is https://www.mscdirect.com/


Using the number 63713408 as an example, I have to search for 63713408 in the search bar and then copy over the: Manufacurer: Cleveland, MFr Part #:(C12204) and description: 5/8", 2MT, 118° Point, High Speed Steel Taper Shank Drill Bit
 

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
To obtain the individual part you can use: https://www.mscdirect.com/product/details/63713408
I copied pertanent information and copied into notepad++ to get rid of the html tags:
Cleveland
5/8", 2MT, 118° Point, High Speed Steel Taper Shank Drill Bit
Oxide Finish, 4-7/8" Flute Length, 8-3/4" OAL, Spiral Flute, Series 2410
Part #: 63713408
Mfr Part #: C12204
Big Book Page #:63

Sounds like you may want Power Query:
https://support.office.com/en-us/ar...er-query-b2725d67-c9e8-43e6-a590-c0a175bd64d8
 

Softsurf105

New Member
Joined
Dec 18, 2018
Messages
14
I tried to initially use a power query but it wasn't working. I used the below code to pull about 5,000 items but now all of the items that are remaining say that there is an error:

Sub Extraction()
Dim dcell As Range
Dim ref As String
Dim oDom As Object: Set oDom = CreateObject("htmlFile")
Dim x As Long, y As Long
Dim oRow As Object, oCell As Object
Dim data
'I put provided sample in A3508:A4000
For Each dcell In Range("A3508:A4000")
ref = dcell.Value
y = 1: x = 1
With CreateObject("msxml2.xmlhttp")
.Open "GET", "http://classcorders.mscdirect.com/Pages/Product.aspx?category=&cat=BDNA&pid=" & ref, False
.Send
oDom.body.innerHtml = .responseText
End With
'Get table 0 from website
With oDom.getElementsByTagName("table")(0)
'Loop through rows
For Each oRow In .Rows
'Loop through cells
For Each oCell In oRow.Cells
'Write content on sheet next to the cell
dcell.Offset(0, x) = oCell.innerText
y = y + 1
Next oCell
y = 1
x = x + 1
Next oRow
End With
Next dcell
End Sub














To obtain the individual part you can use: https://www.mscdirect.com/product/details/63713408
I copied pertanent information and copied into notepad++ to get rid of the html tags:
Cleveland
5/8", 2MT, 118° Point, High Speed Steel Taper Shank Drill Bit
Oxide Finish, 4-7/8" Flute Length, 8-3/4" OAL, Spiral Flute, Series 2410
Part #: 63713408
Mfr Part #: C12204
Big Book Page #:63

Sounds like you may want Power Query:
https://support.office.com/en-us/ar...er-query-b2725d67-c9e8-43e6-a590-c0a175bd64d8
 

Similar threads

Watch MrExcel Video

Forum statistics

Threads
1,127,331
Messages
5,624,050
Members
416,007
Latest member
csf

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