mardi 16 avril 2019

VBA code to copy table data from webpage into Excel

I was trying to copy data from a table on a webpage into Excel using VBA code but didn't get anything on the Excel sheet :(.

I have tried to put together some VBA code from different sources. Here is my code:

Sub CopyWebData()

Dim IE As Object

On Error Resume Next
Application.DisplayAlerts = False

Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .navigate "https://eresearch.fidelity.com/eresearch/evaluate/fundamentals/earnings.jhtml?tab=details&symbols=GOOG"
        Do Until .readyState = 4: DoEvents: Loop
    End With

Dim idoc As MSHTML.HTMLDocument
Dim elem As MSHTML.IHTMLElement

Set idoc = IE.document
Set elem = idoc.getElementsByClassName("layout-outer-table-width")(0).innerText

Sheets("Sheet1").Activate
Range("A1:A1000") = "" ' erase previous data
Range("A1").Select
Range("A1").Value = elem

End Sub

This is a password-protected webpage and I have logged in so I can see the webpage has been successfully pulled out by the VBA code. However, the data in the table on this webpage failed to be copied into excel - I saw nothing on the destination worksheet.

As you can see, I used code .getElementsByClassName("layout-outer-table-width") since I used Chrome's "Inspect" function to check the webpage and found that when the mouse was hovering over the statements:

...<table cellspacing="0" cellpadding="0" border="0" class="layout-outer-table-width"> == $0

 <tbody>...</tbody>

 </table>

part of the webpage covering the table I need was shaded. I then coded in the class name "layout-outer-table-width". However, as I said, I didn't see anything appearing on the Excel sheet.

Any instruction would be much appreciated!




Aucun commentaire:

Enregistrer un commentaire