VBA网页爬取求助：实现下页跳转与分页循环执行

已完成事项 #

• 登录问题[解决]
• 单页表格数据爬取[解决]
• 总页数统计问题[解决]

当前卡点 #

跳转至下页的功能实现

需求说明 #

需实现以下任一方案完成多页数据爬取：

1. 从IE中获取所有页码并写入Excel，以此确定循环次数
2. 基于已解决的总页数统计，实现多页数据爬取的循环逻辑

本人对IE的HTML元素（Class/ID/Tag）识别不熟悉，附当前VBA代码及目标网站的HTML代码、界面截图：

当前VBA代码 #

Public Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal HWND As LongPtr) As LongPtr

Sub Test()
Dim i As Long
Dim URL As String
Dim IE, ObjElement, ObjCollection As Object


Set IE = CreateObject("InternetExplorer.application")

IE.Visible = True

URL = "xxxxx"

IE.Navigate URL

Application.StatusBar = URL & " is loading. Please wait..."

Do While IE.ReadyState = 4: DoEvents: Loop
Do Until IE.ReadyState = 4: DoEvents: Loop

Application.StatusBar = URL & " Loaded"

'---------------------------------------------------

Dim HWNDsrc

HWNDsrc = IE.HWND
SetForegroundWindow HWNDsrc


Dim n As Long

                    For n = 0 To IE.Document.getElementsByTagName("input").Length - 1
                     With IE.Document.getElementsByTagName("input")(n)
                        
                        Select Case (.Name)
                            Case "UserName"
                                .Value = "ABC"
                                
                            Case "Password"
                                .Value = "12345"
                                
                            Case "btn.btn-default"
                                .Click
                                n = 99999
                                
                            Case Else
                                    'ignore
                            
                            End Select
                            End With
                        Next n
On Error Resume Next
IE.Document.forms(0).submit
    
Do While IE.Busy Or IE.ReadyState <> 4: DoEvents: Loop
            
   URL = "xxxxx"

IE.Navigate URL
            
   

Do While IE.Busy Or IE.ReadyState <> 4: DoEvents: Loop

                    For n = 0 To IE.Document.getElementsByTagName("input").Length - 1
                     With IE.Document.getElementsByTagName("input")(n)
                        
                        
                         Select Case (.Name)
                        
                        
                            Case "LookupModel.StartDate"
                                .Value = "06/30/2022"
                        
                            Case "LookupModel.EndDate"
                                .Value = "08/01/2022"
                        
                
                            Case "LookupModel.Station"
                                .Value = "ICN"
                        
                            Case "LookupModel.IsInbound"
                                .Value = "False"
                                .Click
                                n = 99999
                            Case Else
                                    'ignore
                            
                            End Select
                            End With
                        Next n
                        
Do While IE.Busy Or IE.ReadyState <> 4: DoEvents: Loop
                        
IE.Document.forms(0).submit

Do While IE.Busy Or IE.ReadyState <> 4: DoEvents: Loop

Dim HTMLTable As IHTMLElement


    For Each HTMLTable In IE.Document.getElementsByClassName("table")(0).getElementsByTagName("tr")
        With ActiveSheet
            .Range("A" & i).Value = HTMLTable.Children(0).textContent
            .Range("B" & i).Value = HTMLTable.Children(1).textContent
            .Range("C" & i).Value = HTMLTable.Children(2).textContent
            .Range("D" & i).Value = HTMLTable.Children(3).textContent
            .Range("E" & i).Value = HTMLTable.Children(4).textContent
            .Range("F" & i).Value = HTMLTable.Children(5).textContent
            .Range("G" & i).Value = HTMLTable.Children(6).textContent
            
            .Range("H" & i).Value = HTMLTable.Children(7).textContent
            .Range("I" & i).Value = HTMLTable.Children(8).textContent
            .Range("J" & i).Value = HTMLTable.Children(9).textContent
            i = i + 1
        End With
    Next HTMLTable




End Sub

相关截图 #

• IE页面HTML代码截图：
• IE页面界面截图：

内容的提问来源于stack exchange，提问作者Ho Wai Yan Arthur