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

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!

Answer1:

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

人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Code not pulling data after first webpage into excel