Pulling information from web into Excel

Softsurf105

New Member
Joined
Dec 18, 2018
Messages
14
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!

34610
34626
34628
34629
34630
34631
34631
34631
34610
34626
34628
34629
34630
34631
34631
34631

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
This is quick and quite dirty but it works
Code:
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
 
Last edited:
Upvote 0
Where would I paste this into?



This is quick and quite dirty but it works
Code:
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
 
Upvote 0
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'
 
Last edited:
Upvote 0
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:

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?









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'
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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
Back
Top