Click here to Skip to main content
15,794,275 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am trying to get Table Data. See below code.

What I have tried:

Sub ScrapeLondonStockExchangeData()
    Dim URL As String
    Dim HTMLContent As String
    Dim HTMLDoc As Object
    Dim Table As Object
    Dim Row As Object
    Dim Cell As Object
    Dim i As Integer, j As Integer
    Dim CurrentRow As Integer
    ' Define the URL of the webpage to scrape
    URL = ""
    ' Create a new instance of Internet Explorer
    Set HTMLDoc = CreateObject("HTMLFile")
    ' Download the webpage's HTML content
    With CreateObject("MSXML2.ServerXMLHTTP.6.0")
        .Open "GET", URL, False
        .send ""
        HTMLContent = .responseText
    End With
    ' Load the HTML content into the HTML document
    HTMLDoc.body.innerHTML = HTMLContent
    ' Find the table by its class name
    Set Table = HTMLDoc.getElementsByClassName("table_dati")(0) ' Adjust 
                                                     ' the index if needed
    ' Create a new worksheet to store the data
    ActiveSheet.Name = "StockData"
    ' Initialize the row counter
    CurrentRow = 1
    ' Loop through the rows in the table
    For Each Row In Table.Rows
        ' Initialize the column counter
        j = 1
        ' Loop through the cells in each row
        For Each Cell In Row.Cells
            ' Copy the data to the Excel worksheet
            Cells(CurrentRow, j).Value = Cell.innerText
            j = j + 1
        Next Cell
        ' Move to the next row in the Excel worksheet
        CurrentRow = CurrentRow + 1
    Next Row
End Sub
Updated 13-Sep-23 4:28am
Richard MacCutchan 6-Sep-23 11:09am    
And? Do you have an actual question?

1 solution

That call is not returning any data, which implies that the table is not static. If that is the case then you cannot use XML HTTP to extract the data, you will have to use a browser based scraping tool. Internet Explorer has finally retired so Selenium Chrome is probably your best option.

One tutorial on how to do that can be found here -[^]
Share this answer
Richard MacCutchan 6-Sep-23 12:47pm    
I tried that link and it showed a valid list of stock prices. But I suspect the problem may be the "we use cookies" popup that precedes it.
BobbyStrain 6-Sep-23 13:54pm    
Excel will load a web page. Then you can work with the page by loading it in Excel.
CHill60 6-Sep-23 13:56pm    
I know. If you have a working solution for the OP then post it

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900