代码在第一个网页进入excel后没有提取数据 [英] Code not pulling data after first webpage into excel

问题描述

我有以下代码，它们将从网站首页上的表格中提取数据(价格，名称，货币，更改等)

I have the below code that will pull through data from a table on the first page of a website (Price, name, currency, change etc)

Public Sub GetTeamData()
Dim strWebAddress As String
Dim strH2AnchorContent As String
Dim IEDocument As MSHTML.HTMLDocument
Dim objH2 As MSHTML.HTMLHeaderElement
Dim obTable As MSHTML.HTMLTable
Dim objRow As MSHTML.HTMLTableRow
Dim objCell As MSHTML.HTMLTableCell
Dim lngRow As Long
Dim lngColumn As Long

' initialize some variables that should probably better be passed as paramaters or defined as constants
strWebAddress = "https://toolkit.financialexpress.net/santanderam"

dateNow = Now
bExitLoop = False
lngTimeoutInSeconds = 5
Do While Not bExitLoop
  If Now > DateAdd("s", lngTimeoutInSeconds, dateNow) Then Exit Do
Loop

' open page
Set IEDocument = GetIEDocument(strWebAddress)
If IEDocument Is Nothing Then
    MsgBox "Timeout reached opening this address:" & vbNewLine & strWebAddress, vbCritical
    Exit Sub
End If

    Dim ButtonData As Variant
    Set ButtonData = IEDocument.getElementsByClassName("paginator fe-paging-navContainer")

Dim button As MSHTML.HTMLLinkElement
For Each button In ButtonData

   Debug.Print button.nodeName

        button.Click

          ' retrieve anchor element
          Set oTable = IEDocument.getElementById("Price_1_1")
             Debug.Print oTable.innerText

              ' iterate over the table and output its contents
              lngRow = 1
              For Each objRow In oTable.Rows
              lngColumn = 1
              For Each objCell In objRow.Cells
                  Cells(lngRow, lngColumn) = objCell.innerText
                  lngColumn = lngColumn + 1
              Next objCell
              lngRow = lngRow + 1
           Next
         Next button
End Sub

我的问题是我无法从下一页(1..7)中提取数据.

My problem is that I cannot get the data to pull through from the next pages (1..7).

任何人都可以帮助解决上面的问题，为什么不从下一页中提取数据吗? 谢谢！

Can anyone please help with why the above wont pull data through from the next pages? Thank you!

推荐答案

在打开页面的代码段之后，将其余代码替换为以下代码.您可能需要稍微调整一下，但它应该遍历所有可用页面:

After the bit of code that opens the page, replace the rest of the code with the below code. You might have to tweak it a bit but it should go through all available pages:

' Set the object for 'Next' button
Dim oNext As Variant
Set oNext = IEDocument.getElementsByClassName("ui-paging-button ui-state-default ui-corner-all ui-paging-next")

' Loop to go through all pages
Dim bExitMLoop As Boolean: bExitMLoop = False
lngRow = 1
Do While Not bExitMLoop

    ' Get data from current page
    Set oTable = IEDocument.getElementById("Price_1_1")
    For Each objRow In oTable.Rows
        lngColumn = 1
        For Each objCell In objRow.Cells
            Cells(lngRow, lngColumn) = objCell.innerText
            lngColumn = lngColumn + 1
        Next objCell
        lngRow = lngRow + 1
    Next

    ' Check if Next button is available
    If oNext.Length = 0 Then
        bExitMLoop = True
    Else
        oNext.Item(0).Click

        ' Wait for page to refresh (could check the ready state here as well)
        dateNow = Now
        bExitLoop = False
        lngTimeoutInSeconds = 3
        Do While Not bExitLoop
            If Now > DateAdd("s", lngTimeoutInSeconds, dateNow) Then Exit Do
        Loop

        ' Reset 'Next' button object
        Set oNext = Nothing
        Set oNext = IEDocument.getElementsByClassName("ui-paging-button ui-state-default ui-corner-all ui-paging-next")
    End If

Loop

这篇关于代码在第一个网页进入excel后没有提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！