VBA to pull product information

[Softsurf105]

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!

 

[Softsurf105]

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]

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]

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

 

[GR00007]

Any PowerQuery experts want to jump in?