Pulling information from web into Excel

[Softsurf105]

I have a list of about 8,000 part numbers and I need to search by each part number on a website and then copy and paste over a manufacture part no, manufacturer name, and the description. Here is a short list the part numbers, and the website is http://classcorders.mscdirect.com/Pages/Search.aspx?k=34610 . On the website the information is pretty consistent I'm just new to VBA and need some help, thanks!

[TABLE="width: 185"]
<colgroup><col></colgroup><tbody>[TR]
[TD]34610[/TD]
[/TR]
[TR]
[TD]34626[/TD]
[/TR]
[TR]
[TD]34628[/TD]
[/TR]
[TR]
[TD]34629[/TD]
[/TR]
[TR]
[TD]34630[/TD]
[/TR]
[TR]
[TD]34631[/TD]
[/TR]
[TR]
[TD]34631[/TD]
[/TR]
[TR]
[TD]34631[TABLE="width: 185"]
<colgroup><col></colgroup><tbody>[TR]
[TD]34610[/TD]
[/TR]
[TR]
[TD]34626[/TD]
[/TR]
[TR]
[TD]34628[/TD]
[/TR]
[TR]
[TD]34629[/TD]
[/TR]
[TR]
[TD]34630[/TD]
[/TR]
[TR]
[TD]34631[/TD]
[/TR]
[TR]
[TD]34631[/TD]
[/TR]
[TR]
[TD]34631[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Kamolga]

This is quick and quite dirty but it works

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
  [COLOR=#008000]'I put provided sample in A2:A17[/COLOR]
    For Each dcell In Range("A2:A17")
        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
       [COLOR=#008000] 'Get table 0 from website[/COLOR]
        With oDom.getElementsByTagName("table")(0)
               [COLOR=#008000] 'Loop through rows[/COLOR]
                For Each oRow In .Rows
                   [COLOR=#008000] 'Loop through cells[/COLOR]
                    For Each oCell In oRow.Cells
                       [COLOR=#008000] 'Write content on sheet next to the cell[/COLOR]
                        dcell.Offset(0, x) = oCell.innerText
                        y = y + 1
                    Next oCell
                    y = 1
                    x = x + 1
                Next oRow
        End With
    Next dcell
End Sub

 

[WebbyGiants]

Check out how to extract data from multiple webpages with power query at https://www.howtoexcel.org/power-query/how-to-extract-data-from-multiple-webpages/

 

[Softsurf105]

Where would I paste this into?

This is quick and quite dirty but it works

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
  [COLOR=#008000]'I put provided sample in A2:A17[/COLOR]
    For Each dcell In Range("A2:A17")
        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
       [COLOR=#008000] 'Get table 0 from website[/COLOR]
        With oDom.getElementsByTagName("table")(0)
               [COLOR=#008000] 'Loop through rows[/COLOR]
                For Each oRow In .Rows
                   [COLOR=#008000] 'Loop through cells[/COLOR]
                    For Each oCell In oRow.Cells
                       [COLOR=#008000] 'Write content on sheet next to the cell[/COLOR]
                        dcell.Offset(0, x) = oCell.innerText
                        y = y + 1
                    Next oCell
                    y = 1
                    x = x + 1
                Next oRow
        End With
    Next dcell
End Sub

 

[Kamolga]

Where would I paste this into?​

Put your references on a new sheet (A2:A17 for this example). Right-click the sheet name below and click 'code' from the menu.
Now go 'insert' menu on top of the new window and chose 'module' and paste this code in the big top right white part.
You can add 'developer' tab in your excel options / customize ribbon and launch the macro from there. It is also possible to get macro's button in quick access toolbar. Otherwise you can simply put the cursor anywhere in the macro and click the button 'play'

 

[Softsurf105]

WOW!!! this is AMAZING! Thank you so much for your help! I just have one last question. The description comes out perfectly but the second table comes out in one cell so it looks like:

[TABLE="width: 381"]
[TR]
[TD="class: xl64, width: 381"]Class C Part #:34388

Manufacturer:Class C Solutions Group

Mfr. Part #:1/4-20X2-1/2SRMSZ.

Pack Code - Qty:100 - PK

Your Price (USD):TBD

Is there a way to seperate the text so each line goes to its own cell? I tried using text to column, deliminate trick but it only pulled out the first Class C part, do you know how to make this work or another way of performing this task?[/TD]
[/TR]
[/TABLE]

Put your references on a new sheet (A2:A17 for this example). Right-click the sheet name below and click 'code' from the menu.
Now go 'insert' menu on top of the new window and chose 'module' and paste this code in the big top right white part.
You can add 'developer' tab in your excel options / customize ribbon and launch the macro from there. It is also possible to get macro's button in quick access toolbar. Otherwise you can simply put the cursor anywhere in the macro and click the button 'play'

 

[Kamolga]

Hi,

-Select column that you want to split
-'data' tab
-'text to column'

-'delimited' and next
​

-tick 'Other' and type CTRL+J in the box. Nothing will appear but you should see the columns properly split

 

[Softsurf105]

That worked! There is one last thing that I would need it to do. there are some items that are in a catalog so it will have to search through this catalog (https://classccatalogs.mscdirect.com/app.php?RelId=6.10.6.0.6) and then select the part number and then copy over the description.

Example is part # 42706
It would search for 42706 on https://classccatalogs.mscdirect.com/app.php?RelId=6.10.6.0.6 and then select it and then copy over the full description "SCREW, CAP, M3, 0.5 MM, 25 MM, SOCKET, THROUGH-HARDENED MEDIUM CARBON ALLOY STEEL, ISO 4762, 12.9, NON-PLATED"

Is there a way to add this to the code?

Thanks!

Hi,

-Select column that you want to split
-'data' tab
-'text to column'

-'delimited' and next
​

-tick 'Other' and type CTRL+J in the box. Nothing will appear but you should see the columns properly split