Vba

[Softsurf105]

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

 

[Michael M]

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]

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]

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

With oDom.getElementsByTagName("table")(0)

When they updated the website, I'd suggest they changed the tables as well, which is usual !!

 

[Softsurf105]

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

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]

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]

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]

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]

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]

Unfortunately, I can't help....but you could try changing the table No in the code !!