Extracting data from website using VBA

Question

I want to extract the projectstatus of a project which I can find on a website. See below for an example how the html is parsed. I want to extract the text Start which is the text between td and /td. See below the html my code.

 <div id="ProjectStatus">
 <tr>
 <th>
 <span id="ProjectStatus_Label1" title="De status van het project">Projectstatus</span>
 </th>
 <td>Start</td>
 </tr>

Below you'll find the code that I have at this moment. This code only gives me the string "Projectstatus", which is not what I want. How can I extract the word "Start"?

Private Sub btnClick()

Dim ieApp As InternetExplorer
Set ieApp = New InternetExplorer
Set ieApp = CreateObject("internetexplorer.application")

With ieApp
 .Navigate "url"
 .Visible = True
End With

Do While ieApp.Busy
    DoEvents
Loop 

Set getStatus = ieApp.Document.getElementById("ProjectStatus_Label1")

strStatus = getStatus.innerText

MsgBox (strStatus) 'gives met the text "Projectstatus, but I need the text "Start"

ieApp.Quit
Set ieApp = Nothing

End Sub

Erik A · Accepted Answer · 2018-01-31 17:47:05Z

1

Achieving this, starting from the ProjectStatus_Label1, will require some DOM navigation.

Use the following:

Do While ieApp.Busy
    DoEvents
Loop
Set labelSpan = ieApp.Document.getElementById("ProjectStatus_Label1")
Set tableHeader = labelSpan.Parent
Set tableRow = tableHeader.Parent
For Each child In tableRow.Children
    If child.tagName = "TD" 'This is the element you're looking for
         Debug.Print child.innerText
         Exit For
    End If
Next

Of course, I highly recommend you revise this code and use explicit declarations and Option Explicit, but you haven't in your question so I won't in my answer.

Also, I've used a number of assignments (labelSpan, tableHeader) for demonstrative purposes. You can use Set tableRow = ieApp.Document.getElementById("ProjectStatus_Label1").Parent.Parent and remove those other declarations.

Or you can use the code-golfy, harder-to-understand approach, starting from the ProjectStatus div:

Debug.Print ieApp.Document.getElementById("ProjectStatus").GetElementsByTagName("td")(0).innerText

edited Jan 31, 2018 at 17:47

answered Jan 31, 2018 at 16:51

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

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

2 Comments

Marten Onnink Over a year ago

Hi Erik, the code you provided works. I had to change .Parent into .ParentElement. Thanks!

Erik A Over a year ago

That's somewhat strange. We must be using a different version of IE (I was working with IE11, and using ShDocVw.InternetExplorer with late binding instead of CreateObject). It certainly is .Parent for me. Documentation on IE automation is sparse, and there's a lot of version incompatibility, so it's mostly trial and error. Glad it worked.

Collectives™ on Stack Overflow

Extracting data from website using VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related