VBA Pulling Wrong Data from table

Softsurf105

New Member
Joined
Dec 18, 2018
Messages
14
I'm using the below VBA code and I want it to pull the MFR, Part No, and description but with the below code it's pulling the quantity, description, and price. It looks like the website it's pulling from is written in Java, I think this is the code for the table


<tbody>
[TD="class: line-content"] [/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] <table class="ui compact very basic table">[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] <tbody>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] [/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] <tr>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] <td><img src=" //cdn.mscdirect.com/global/application-content/images/msc-logo-mini.JPG " width="36" style="align-inline-img"> Part #:</td>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] <td>60267044</td>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] </tr>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] [/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] <tr>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] <td>Mfr Part #:</td>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] <td>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] <span>603</span>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] [/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] </td>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] </tr>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] <!-- Big Book No --><!-- Big Book No --><!-- Country Of Origin --><!-- End: Country of origin -->[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] [/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] </tbody>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"] </table>[/TD]

[TD="class: line-number"][/TD]
[TD="class: line-content"]</div>

This is the VBA code:[/TD]

</tbody>



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("I3508:I4000")
ref = dcell.Value
y = 1: x = 1
With CreateObject("msxml2.xmlhttp")
.Open "GET", "https://www.mscdirect.com/browse/tn/?searchterm= " & 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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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