Need to pull details specific details from web page using vba

Question

I need to pull the text 'Catalog Manager/ Sales' & 'EOL (product/component)' from the view source tab using VBA.

Below is the view source code:

<tr>
    <td nowrap="true" valign="top" width="165px" class="ms-formlabel"><h3 class="ms-standardheader"><a name="SPBookmark_Requesting_x0020_Group"></a>Requesting Group</h3></td>
    <td valign="top" class="ms-formbody" width="450px" id="SPFieldChoice">
        <!-- FieldName="Requesting Group"
             FieldInternalName="Requesting_x0020_Group"
             FieldType="SPFieldChoice"
          -->
        Catalog Managers/ Sales
    </td>
</tr>

<tr>
    <td nowrap="true" valign="top" width="165px" class="ms-formlabel"><h3 class="ms-standardheader"><a name="SPBookmark_Reason_x0020_for_x0020_change"></a>Reason for change</h3></td>
    <td valign="top" class="ms-formbody" width="450px" id="SPFieldChoice">
        <!-- FieldName="Reason for change"
             FieldInternalName="Reason_x0020_for_x0020_change"
             FieldType="SPFieldChoice"
          -->
        EOL (product/component)
    </td>
</tr>

There are multiple id="SPFieldChoice" and i need to pull the details only for 'Requesting Group' and 'Reason for change'.

I am writing below code to fetch the details in excel but it is not specific to my requirements.

Set hcol = ie.document.getElementsByTagName("td")
    For Each inps In hcol
        If inps.ID = "SPFieldChoice" Then
            Sheets("Sheet2").Range("A" & j).Value = inps.innerText
        End If
    Next

Need a code which can pull only the required details mentioned above.

I am new to vba but after researching about nodes in google, i can say 'YES' there are many nodes in the code. using my above code i can fetch all the text (for ex. EOL (product/component)) in a different sheet and then i can fetch the required text in my main sheet. But that doesnt seem to be the best way to make my macro work. — gaurav koli, Jul 02 '15 at 10:11

paul bica · Accepted Answer · 2015-07-03T11:17:43.430

0

This is just a suggestion (I can't verify the answer) but I wanted to illustrate what you could try:

Set hcol = Set hcol = ie.document.getElementsByTagName("td")

For Each inps In hcol
    If inps.ID = "SPFieldChoice" Then
        If InStr(1, inps.innerHTML, "Requesting Group") > 0 Or InStr(1, it, "Reason for change") > 0 Then
            Sheets("Sheet2").Range("A" & j).Value = inps.innerText
        End If
    End If
Next

edited Jul 03 '15 at 11:17

answered Jul 02 '15 at 21:36

paul bica

10,557
4
23
42

the above code doesnt seems to be working. Is there any way to move on to the next Node. `codeSet hcol = ie.document.getElementsByTagName("td") For Each inps In hcol If inps.innerText = "Requesting Group" Then **'move to next node** 'Sheets("Sheet2").Range("B" & j).Value = inps.innertext j = j + 1 End If Nextcode` – gaurav koli Jul 03 '15 at 11:00
it doesnt pull anything – gaurav koli Jul 03 '15 at 11:04
for **it** variable it pulls "Catalog Manager/Sales" and then it doesnt satisfy the IF condition mentioned and exits IF and searches for next inps.id = "SPFieldChoice" – gaurav koli Jul 03 '15 at 11:08
I made some changes, please try it now – paul bica Jul 03 '15 at 11:18

Need to pull details specific details from web page using vba

1 Answers1