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