VBA Webscrape HTML Coinmarketcap

Question

Trying to scrape the number of cryptos in the top left corner of https://coinmarketcap.com/. I tried to find the "tr" but could not. Not sure how to grab that value up the top left of the page.

Here is what I have so far and I am being thrown a runtime error 438 Object doesn't support this property or method.

 Sub cRYP()

 Dim appIE As Object
 Set appIE = CreateObject("internetexplorer.application")

  With appIE
  .Navigate "https://coinmarketcap.com/"
  .Visible = True
 End With

 Do While appIE.Busy
 DoEvents
 Loop
 Set allRowOfData = appIE.Document.getElementById("__next")

  Dim myValue As String: myValue = 
  allRowOfData.Cells(16).innerHTML

  appIE.Quit

  Range("A1").Value = myValue

  End Sub

Answer 1

There is no tr tag, because there is no table. At first you must get the html structure which contains your wanted value, because there is no possibility to get it directly. That is the structure with the classname container. Because the method getElementsByClassName() builds a node collection you must get the right structure with it's index in the collection. That's easy because its the first one. The first index of a collection is 0 like in an array.

Than you have this html structure:

<div class="container">
  <div><span class="sc-2bz68i-0 cVPJov">Cryptos
      <!-- -->: &nbsp;<a href="/" class="cmc-link">17.826</a>
    </span><span class="sc-2bz68i-0 cVPJov">Exchanges
      <!-- -->: &nbsp;<a href="/rankings/exchanges/" class="cmc-link">459</a>
    </span><span class="sc-2bz68i-0 cVPJov">Market Cap
      <!-- -->: &nbsp;<a href="/charts/" class="cmc-link">€1,536,467,483,857</a>
    </span><span class="sc-2bz68i-0 cVPJov">24h Vol
      <!-- -->: &nbsp;<a href="/charts/" class="cmc-link">€105,960,257,048</a>
    </span><span class="sc-2bz68i-0 cVPJov">Dominance
      <!-- -->: &nbsp;<a href="/charts/#dominance-percentage" class="cmc-link">BTC
        <!-- -->:
        <!-- -->42.7%
        <!-- -->&nbsp;
        <!-- -->ETH
        <!-- -->:
        <!-- -->18.2%
      </a>
    </span><span class="sc-2bz68i-0 cVPJov"><span class="icon-Gas-Filled" style="margin-right:4px;vertical-align:middle"></span>ETH Gas
      <!-- -->: &nbsp;<a>35
        <!-- -->
        <!-- -->Gwei<span class="sc-2bz68i-1 cEFmtT icon-Chevron-down"></span>
      </a>
    </span></div>
  <div class="rz95fb-0 jKIeAa">
    <div class="sc-16r8icm-0 cPgeGh nav-item"></div>
    <div class="rz95fb-1 rz95fb-2 eanzZL">
      <div class="cmc-popover">
        <div class="cmc-popover__trigger"><button title="Change your language" class="sc-1kx6hcr-0 eFEgkr"><span class="sc-1b4wplq-1 kJnRBT">English</span><span class="sc-1b4wplq-0 ifkbzu"><span class="icon-Caret-down"></span></span></button></div>
      </div>
    </div>
    <div class="rz95fb-1 cfBxiI">
      <div><button title="Select Currency" data-qa-id="button-global-currency-picker" class="sc-1kx6hcr-0 eFEgkr"><span class="sc-1q0bpva-0 hEPBWj"></span><span class="sc-1bafwtq-1 dUQeWc">EUR</span><span class="sc-1bafwtq-0 cIzAJN"><span class="icon-Caret-down"></span></span></button></div>
    </div><button type="button" class="sc-1kx6hcr-0 rz95fb-6 ccLqrB cmc-theme-picker cmc-theme-picker--day"><span class="icon-Moon"></span></button>
  </div>
</div>

As you can see the wanted value is part of the first a tag in the scraped structure. We can simply get that tag with the method getElementsByTagName(). This will also build a node collection. We need also the first element of the collection with the index 0.

Than we have this:

<a href="/" class="cmc-link">17.826</a>

Now we only need the innertext of this element and that's it.

Here is the VBA code. I don't use the IE, because it is finaly EOL and shouldn't be used anymore. You can load coinmarketcap simply without any parameters via xhr (xml http request):

Sub CryptosCount()

  Const url As String = "https://coinmarketcap.com/"
  Dim doc As Object
  Dim nodeCryptosCount As Object
  
  Set doc = CreateObject("htmlFile")
  
  With CreateObject("MSXML2.XMLHTTP.6.0")
    .Open "GET", url, False
    .Send
    
    If .Status = 200 Then
      doc.body.innerHTML = .responseText
      Set nodeCryptosCount = doc.getElementsByClassName("container")(0).getElementsByTagName("a")(0)
      MsgBox "Number of cryptocurrencies on Coinmarketcap: " & nodeCryptosCount.innertext
    Else
      MsgBox "Page not loaded. HTTP status " & .Status
    End If
  End With
End Sub

Edit

As I see now, there is a possibility to get the value directly by using
getElementsByClassName("cmc-link")(0)

You can play with the code to learn more.