jeudi 18 avril 2019

VBA code to copy table from webpage into Excel

I have modified the code to try to get a sequence of similar tables. However, these tables copied into respective sheets are exactly the same, that is, the table for the first variable/sheet has been replicated to the other sheets which are created for different variables - the tables should be different on different sheets. What's wrong with my new code? Your advice again would be very appreciated!

Sub CopyWebTable()

Dim IE As InternetExplorer, hTable As Object, clipboard As Object, t As Date
Dim Var As String
Const MAX_WAIT_SEC As Long = 5

For i = 1 To 3
Var = ThisWorkbook.Worksheets("Par").Range("B" & i + 2)

Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Set IE = New InternetExplorer

With IE
    .Visible = True
    .Navigate2 "https://eresearch.fidelity.com/eresearch/evaluate/fundamentals/earnings.jhtml?tab=details&symbols=" & Var

    While .Busy Or .readyState < 4: DoEvents: Wend

    t = Timer                                'timed loop for details table to be present
    Do
        On Error Resume Next
        Set hTable = IE.document.querySelector(".earningsHistoryTable-Cont table")
        On Error GoTo 0
        If Timer - t > MAX_WAIT_SEC Then Exit Do
    Loop While hTable Is Nothing
    If Not hTable Is Nothing Then  'use clipboard to copy paste
        clipboard.SetText hTable.outerHTML
        clipboard.PutInClipboard
        ThisWorkbook.Worksheets(Var).Range("A1").PasteSpecial

    End If
End With
Next i

End Sub




Aucun commentaire:

Enregistrer un commentaire