CODEWITHSUNDEEP
excelvba
ActuallyJane
ActuallyJane

Reputation: 55

VBA - Error using variable in getElementsByClassName

I'm getting an "Object doesn't support this property or method" (Run-time error '438') error when I try to use a variable to specify which element in a list of classes to use.

For tdNum = 0 To 1000
    If document.getElementsByClassName("prod-somm")(tdNum).getElementById("no-piece").innerText = ItemNbr Then
            Cells(cell, 2).Value = document.getElementsByClassName("prod-somm")(tdNum).getElementById("col-action").getElementsByTagName("span")(0).innerText
            Exit For
    End If
Next tdNum

HTML:

<table align="center" cellspacing="0" class="prod-somm">
    <tbody>
        <tr>
            <td align="center" rowspan="2" class="prod-somm-image"></td>
            <td class="prod-somm-texte" valign="top">
                <a href="/eng/Balance-of-system/Fuse-and-holder/Wohner-31110.000/p/284" id="no-piece">
                    90-FT017
                    <span class="prod-somm-sepno"></span>
                    <span id="panier_fab_284">Wohner</span>
                    <span id="panier_nomanufact_284">31110.000</span>
                </a>
            <a href="/eng/Balance-of-system/Fuse-and-holder/Wohner-31110.000/p/284"></a>
            </td>
        </tr>
    <tr>
        <td id="col-action">
            <div class="prix">
                <span id="panier_prix_284">10.43</span>
            </div>
        </td>
    </tr>
</table>

The problem occurs at If document.getElementsByClassName("prod-somm")(tdNum).getElementById("no-piece").innerText = ItemNbr Then

The goal is to make the program run down a list of search results until it finds the item number that matches the one in my Excel spreadsheet, then take the price that corresponds to that item.

EDIT: Here is the entire code. Might make what I'm trying to do a little more clear.

Option Explicit

Function priceGetRematek()

 Dim XMLHttpRequest As New MSXML2.XMLHTTP60
 Dim xhr As MSXML2.XMLHTTP60
 Dim cell As Integer
 Dim tdNum As Integer
 Dim ItemNbr As String
 Dim document As MSHTML.HTMLDocument

    'Login to Rematek
  With XMLHttpRequest
   .Open "POST", "https://rematek-energie.com/eng/customer-login/account-authentication.php", False
   .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
   .send "name_se_connecter=se_connecter&zebra_honeypot_se_connecter=&[email protected]&motpasse=password&connexion=Sign in"
  End With

'Debug.Print XMLHttpRequest.responseText

    'Get Price Element from HTML
  Set xhr = New MSXML2.XMLHTTP60

  For cell = 2 To 38

      ItemNbr = Cells(cell, 1).Value

      With xhr

          .Open "POST", "https://rematek-energie.com/eng/pg/1/r/" & ItemNbr, False
          .send
'Debug.Print xhr.responseText

          If .readyState = 4 And .Status = 200 Then
                Set document = New MSHTML.HTMLDocument
                document.body.innerHTML = .responseText

                For tdNum = 0 To 1000
                    If document.getElementsByClassName("prod-somm")(tdNum).getElementById("no-piece").innerText = ItemNbr Then
                        Cells(cell, 2).Value = document.getElementsByClassName("prod-somm")(tdNum).getElementById("col-action").getElementsByTagName("span")(0).innerText
                        Exit For
                    End If
                Next tdNum

          Else
                MsgBox "Error" & vbNewLine & "Ready state: " & .readyState & vbNewLine & "HTTP request status: " & .Status
          End If

      End With

  Next cell

End Function

Upvotes: 4

Views: 3437

Answers (1)

Florent B.
Florent B.

Reputation: 42528

The method getElementById is only available on the html document and not on an html element. But since you have duplicated ids, your best option here is probably to get the targeted element with querySelector. Note that the standard impose a unique id, but it is not enforced in the browsers.

Here is an example that should get you started:

' execute the query
Dim xhr As New MSXML2.XMLHTTP60
xhr.Open "GET", "https://rematek-energie.com/eng/pg/1/r/5", False
xhr.send

' load the html
Dim html As New HTMLDocument, html2 As Object
Set html2 = html
html2.write xhr.responseText

' scrap the html
Dim elements As MSHTML.IHTMLElementCollection
Dim element As MSHTML.IElementSelector
Dim link As IHTMLElement

Set elements = html.getElementsByClassName("prod-somm")
For i = 0 To elements.Length - 1
  Set element = elements(i)

  ' get the link with id="no-piece"
  Set link = element.querySelector("a[id='no-piece']")
  If Not link Is Nothing Then

    ' display the product code
    Debug.Print link.FirstChild.data

  End If

Next

Upvotes: 1

Related Questions