Softsurf105

New Member
Joined
Dec 18, 2018
Messages
14
I've used the below is the code that I have used to pull information based off a manufacturer part #. I’m trying to use the MSC part (there are 6,000 items total but 63687313 is an actual example) pull the MFR, MFR part no and description from the following website https://www.mscdirect.com/

I keep getting an error when I run this code and I'm not sure why, can someone please help.





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

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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,042
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
What error do you get ??
I'd suggest that the website has changed slightly and no longer works.
Visit the site manually and copy paste the web address back into your code.
 

Softsurf105

New Member
Joined
Dec 18, 2018
Messages
14
What error do you get ??
I'd suggest that the website has changed slightly and no longer works.
Visit the site manually and copy paste the web address back into your code.


Micheal! Great catch, I updated the website but I'm not pulling the correct information. It's pulling over Description in one cell, price in the next and quantity in the last cell. Could you help me write the code so that it captures the manufacturer name, part number and description?

I'm just starting to learn this and I'm not sure how to manipulate the code just yet.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,042
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Bit hard for me without knowledge of the website or your spreadsheet, but I'm guessing you will need to modify this bit

Code:
With oDom.getElementsByTagName("table")(0)
When they updated the website, I'd suggest they changed the tables as well, which is usual !!
 

Softsurf105

New Member
Joined
Dec 18, 2018
Messages
14

ADVERTISEMENT

Bit hard for me without knowledge of the website or your spreadsheet, but I'm guessing you will need to modify this bit

Code:
With oDom.getElementsByTagName("table")(0)
When they updated the website, I'd suggest they changed the tables as well, which is usual !!


will the table code be in the website?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,042
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
The Table will be in the website, but You probably need to original writer of the code to modify it to match the new "table"
I can't access the website, so can't help much there.
I'd suggest finding the part of the website that holds all the MFR's, whatever they are, would be a good starting point
 

Softsurf105

New Member
Joined
Dec 18, 2018
Messages
14

ADVERTISEMENT

The Table will be in the website, but You probably need to original writer of the code to modify it to match the new "table"
I can't access the website, so can't help much there.
I'd suggest finding the part of the website that holds all the MFR's, whatever they are, would be a good starting point

Here is the website! https://www.mscdirect.com/browse/tn/?searchterm
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,042
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
As I mentioned..... I can't access the website !!
AND as I mantioned what is a MFR....that is where you need to start looking for the Table(0)
When you get into the MFR section, you can right click on a table and "Inspect" the code
 

Softsurf105

New Member
Joined
Dec 18, 2018
Messages
14
As I mentioned..... I can't access the website !!
AND as I mantioned what is a MFR....that is where you need to start looking for the Table(0)
When you get into the MFR section, you can right click on a table and "Inspect" the code


MFR is the manufacturer and the MFR # is the manufacturer part number. I looked in the code and I found 10 sections that list the MFR, how do I know which of the 10 sections that mention MFR to pull from?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,042
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Unfortunately, I can't help....but you could try changing the table No in the code !!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,299
Messages
5,623,846
Members
415,995
Latest member
SergioCM92

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