VBA : Retrieve label value from HTML to MsgBox

Question

I have following HTML code, that I want to retrieve data from:

<div class="span4">
    <div>
       <label for="Game_type">Portal Games</label>
         XXX
    </div>
    <div>
        <label for="Game_Reference">Game reference</label>
         22130903
    </div>
    <div>
        <label for="Release_Date">Release Date</label>
         2015-07-13
    </div>
    <div>
        <label for="Prise">Prise</label>
         USD 90,00
    </div>
    <div>
        <label for="Game_Rank">Game Rank</label>
          4
    </div>
</div>

How I am able to get all those label values/at least one value into MsgBox?. (Later I will input them into Excel myself)

I have tried using following code to get first value:

Dim IE As Object
Set IE = CreateObject("INTERNETEXPLORER.APPLICATION")
'page address is stated in code
IE.navigate "page name" 
IE.Visible = True

While IE.Busy
'Wait until IE is busy and loading page
DoEvents
Wend

Set gtype = IE.Document.getElementsByClassName("span4")(0).getElementsById("Game_type")
GtypeValue =  gtype.Value
MsgBox (GtypeValue)

End Sub

I received run-time error "91:"

Object variable or With Block variable not set.

150904 Hopefully last one question, regarding this topic.Default code looks like

 strCont = objIE.Document.getElementsByClassName("span4")(0).innerHTML

But I want to have a variable instead of "span4", in example Dim1= "span4" I state following:

strCont = "objIE.Document.getElementsByClassName(" & Chr(34) & Dim1 & Chr(34) & ")(0).innerHTML"

It does not work, empty value in MsgBox. How can I make sure that this sting will be counted as exact code to be executed later in step:

Set objMatches = .Execute(strCont)

Are you coding your VBA in Excel? And what line are you receiving the error on? — MatthewD, Sep 01 '15 at 15:01
It seems the very first line of your subroutine is missing, is this the problem? — laylarenee, Sep 01 '15 at 15:05
There are no id's in that HTML, so getElementById isn't going to do anything. Try `getElementsByTagName("div")` instead: you'll need to loop over the returned collection. — Tim Williams, Sep 01 '15 at 16:21
@MatthewD - yes, In Excel. In line: Set gtype = IE.Document.getElementsByClassName("span4")(0).getElementsById("Game_type") — Максим Музыкин, Sep 02 '15 at 08:19
Could you please share the full code you are trying to get to work, put it next to "Default code looks like ...". It's not clear what is "empty value in MsgBox", show your test MsgBox in that code. — omegastripes, Sep 04 '15 at 18:01
Try `strCont = objIE.Document.getElementsByClassName(Dim1)(0).innerHTML` — omegastripes, Sep 04 '15 at 18:06

score 0 · Accepted Answer · edited May 23 '17 at 12:22

0

Why not to try regex for parsing?

Sub MsgGameType()
    Dim objIE As Object
    Dim strCont As String
    Dim objMatches As Object
    Dim objMatch As Object

    Set objIE = CreateObject("InternetExplorer.Application")
    'page address is stated in code
    objIE.Navigate "page name"
    objIE.Visible = True

    Do While objIE.Busy Or Not objIE.readyState = 4
        DoEvents
    Loop
    Do Until objIE.document.readyState = "complete"
        DoEvents
    Loop

    strCont = objIE.document.getElementsByClassName("span4")(0).innerHtml
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "<div>\s*<label for="".*?"">(.*?)</label>\s*(.+?)\s*?</div>"
        Set objMatches = .Execute(strCont)
        For Each objMatch In objMatches
            MsgBox objMatch.SubMatches(0) & " = " & objMatch.SubMatches(1)
        Next
    End With
End Sub

See XHTML parsing with RegExp disclaimer.

edited May 23 '17 at 12:22

Community

1
1

answered Sep 01 '15 at 21:16

omegastripes

12,351
4
45
96

It actually works. Thank you so much. Just one small thing: I am completely new to RegExp, and I'm having problem with "Patter" part. Can you please explain how it is structured and why? Also what If I have more than I div inside of HTML code with name "span4" – Максим Музыкин Sep 02 '15 at 08:18
Here is [the description of `.Pattern` property on MSDN](https://msdn.microsoft.com/en-us/LIbrary/f97kw5ka.aspx), and [this one](http://script-coding.com/WSH/RegExp.html#4.) I guess might be more appropriate. – omegastripes Sep 02 '15 at 08:54
If you have nested divs, please update your question with such html block, the code you have tried, it's resulting output and what's going wrong. – omegastripes Sep 03 '15 at 05:08
I have already found a way how to retrieve info from nested DIVs. In line: IE.Document.getElementsByClassName("span4")(0).innerHTML" I have just replaced "span4" with variable that will change according to other code. Same applies to line MsgBox objMatch.SubMatches(0) & " = " & objMatch.SubMatches(1) I have just replaced number value after SubMatches and got what I wanted. Anyhow - thank you for your help. – Максим Музыкин Sep 03 '15 at 06:49
Hopefully last one question, regarding this topic.Default code looks like: strCont = objIE.Document.getElementsByClassName("span4")(0).innerHTML But I want to have a variable instead of "span4", in example Dim1= "span4", I state following: strCont = "objIE.Document.getElementsByClassName(" & Chr(34) & Dim1 & Chr(34) & ")(0).innerHTML" It does not work. How can I make sure that this sting will be counted as exact code to be executed later in step: Set objMatches = .Execute(strCont) – Максим Музыкин Sep 04 '15 at 08:15
Please update your question with that code to make it readable and with description and requirements. I'll take a look and update my answer. – omegastripes Sep 04 '15 at 09:01

VBA : Retrieve label value from HTML to MsgBox

1 Answers1