Excel VBA Scrape HTML table for text

Question

Alright,

I've spent hours searching how to grab text from a table and for the life of me I havn't come across a method that worked in my case.

Here is a sample of the HTML that Im trying to get information from

<table class="empDetailCard foldable unfolded">
    <tr>
        <td colspan="4" class="title">
            <span class="fold-control">full name</span>
        </td>
    </tr>
    <tr class="fold-row">
        <td>
            <div class="badgePhoto reg">
    <img class="photo " src="removed" />
</div>
</td>
        <td>
           <span class="line">
               <span class="section-title">Employee Info</span>
           </span>
           <div class="employeeInfo">
               <div>
                   <span class="line">
                       <span class="row-label">Login</span>
                       mylogin</span>
                   <span class="line">
                       <span class="row-label">Empl ID</span>
                       1234567</span>
                   <span class="line">
                       <span class="row-label">Badge</span>
                       1234567</span>
                   <span class="line">
                       <span class="row-label">Dept ID</span>
                       1234567</span>
                   <span class="line">
                       <span class="row-label">Location</span>
                       1234567
                       </span>
                   <span class="line">
                       <span class="row-label">Manager</span>
                       <a href="removed" 
                    title="">John, Smith</a>
                    </span>
        </td>
    </tr>
</table>

I've tried to grab "mylogin" from the Login table using GetElementByID, GetElementByName, and even regex but I've had no luck.

Function IdtoLogin(empID As String)
     Dim H As Object, html As Object, objResult As Object
     Set H = CreateObject("WinHttp.WinHttpRequest.5.1")
     H.Open "GET", "myurl" & empID
     H.setRequestHeader "Content-Type", "text/xml"
     H.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
     H.SetAutoLogonPolicy 0
     H.send

     Set html = New HTMLDocument
     html.Body.innerHTML = H.ResponseText
     Set objResult = html.GetElementById("Login")
     IdtoLogin = objResult.innerHTML

End Function

The response returns the correct HTML with the login information but it fails to get the element ID and throws a "runtime error 91". If someone could point out the obvious for me thatd be great because Im going crazy.

you have to do getelementsbyClassName then compare innertext to "Login" — jsotola
– jsotola, Commented Sep 26, 2017 at 5:09
I've tried getelementsbyclassname but it throws a runtime error 438 — Josh Henderson
– Josh Henderson, Commented Sep 26, 2017 at 6:08
I've updated the main post to show more of the HTML from the page Im trying to get information from. — Josh Henderson
– Josh Henderson, Commented Sep 26, 2017 at 6:35

QHarr · Accepted Answer · 2018-06-08 11:03:46Z

1

Try a CSS selector

html.querySelector("div.employeeInfo span")

The info you want maybe part of outerHTML for example. By using singular querySelector you get the first node match which, in the HTML shown, is mylogin:

answered Jun 8, 2018 at 11:03

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Scrape HTML table for text

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related