Import Web Data to Excel and Convert to Table

0

First off I want to reference this link to a Youtube video put up by TeachExcel: https://www.youtube.com/watch?v=IOzHacoP-u4.

This video explains how to import HTML data from a website and put it in Msgbx. It uses the following code (with some minor exceptions as I am referencing a different website)

CODE

Sub Get_Web_Data()

' TeachExcel.com

Dim request As Object

Dim response As String

Dim html As New HTMLDocument

Dim website As String

Dim price As Variant

' Website to go to.

website = "https://coinmarketcap.com/currencies/bitcoin/"

' Create the object that will make the webpage request.

Set request = CreateObject("MSXML2.XMLHTTP")

' Where to go and how to go there - probably don't need to change this.

request.Open "GET", website, False

' Get fresh data.

request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"

' Send the request for the webpage.

request.send

' Get the webpage response data into a variable.

response = StrConv(request.responseBody, vbUnicode)

' Put the webpage into an html object to make data references easier.

html.body.innerHTML = response

' Get the price from the specified element on the page.

price = html.getElementsByClassName("cmc-details-panel-about__table")(0).innerText

'   Dim price As TableObject

' Output the price into a message box.

   MsgBox price

'   InputBox "Copy the below text", "Copy Text", price

End Sub

CODE

As you can see, the code I am trying to reference is an entire table. It is impossible to take it line by line because the div class is on one line and there are several div lines beneath it which include multiple lines of information from a table. I would like to convert this into a table in Excel, rather than display it in a msgbx. 

I have attached a picture of the table I wish to get the data from, and I have attached a picture of the code screen which populates once I inspect the element which is the Bitcoin price.

Answer
Discuss



Answer the Question

You must create an account to use the forum. Create an Account or Login