需VBA代码获取网页活动参会者数据，IE对象属性无法返回内容求解决

我想要获取网页上某活动的参会者名单，通过浏览器右键选择“查看页面源代码”可以看到参会者姓名。尝试用VBA的IE对象，通过objIE.Document.body.innerText、objIE.Document.body.innerHTML、objIE.Document.body.textContent三个属性均无法返回参会者姓名，怀疑页面存在动态加载机制，请问该如何解决？

尝试的VBA代码：

Public Sub OpenWebsiteAndRetrieveData()
    Dim objIE As Object
    Dim strURL As String
    Dim strinnerText As String
    Dim strinnerHTML As String
    Dim strTextContent As String
        
    ' Initialize the URL
    strURL = "https://meetup.com/cflfreethought/events/301401141/attendees/"
    
    ' Create an Internet Explorer instance
    Set objIE = CreateObject("InternetExplorer.Application")
    
    ' Navigate to the URL
    objIE.Navigate strURL
    
    ' Wait for the page to fully load
    Do While objIE.Busy Or objIE.ReadyState <> 4
        DoEvents
    Loop
    
    ' Make the Internet Explorer visible (optional)
    objIE.Visible = True
    
    ' Retrieve the inner text of the webpage
    strinnerText = objIE.Document.body.innerText
    strinnerHTML = objIE.Document.body.innerHTML
    strTextContent = objIE.Document.body.textContent
            
    ' Output the content (for demonstration purposes)
    Debug.Print "innerText:", Len(strinnerText), InStr(strinnerText, "David")
    Debug.Print "innerHTML:", Len(strinnerHTML), InStr(strinnerHTML, "David")
    Debug.Print "TextContent:", Len(strTextContent), InStr(strTextContent, "David")
        
    ' Clean up
    objIE.Quit
    Set objIE = Nothing
    
    Stop
    
    MsgBox "Done"
End Sub    

1. 优化页面等待逻辑，确保动态内容加载完成 #

标准的ReadyState=4仅表示页面初始资源加载完成，但动态渲染的内容可能还在加载。可以通过以下两种方式补充等待：

方法A：等待目标元素出现 #

假设参会者名单在class为attendee-item的元素中，添加循环等待直到该元素存在，同时加入超时机制避免无限等待：

' 在原等待代码后添加
Dim startTime As Double
startTime = Timer
Do While objIE.Document.getElementsByClassName("attendee-item").Count = 0
    DoEvents
    ' 超时30秒则退出循环
    If Timer - startTime > 30 Then Exit Do
Loop

方法B：添加固定延迟等待 #

先在模块顶部声明Sleep API：

#If VBA7 Then
    Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

然后在原等待代码后添加延迟，让JS完成动态渲染：

Sleep 5000 ' 等待5秒

2. 直接定位参会者所在的DOM元素 #

不要直接获取整个body内容，而是定位到存放参会者名单的容器元素，精准提取数据：

' 替换原获取内容的代码
Dim attendeesContainer As Object
Set attendeesContainer = objIE.Document.querySelector(".attendee-list") ' 假设容器class为attendee-list
If Not attendeesContainer Is Nothing Then
    ' 遍历每个参会者元素提取姓名
    Dim attendeeItem As Object
    For Each attendeeItem In attendeesContainer.getElementsByClassName("attendee-item")
        Debug.Print "参会者姓名:", attendeeItem.querySelector(".attendee-name").innerText
    Next
End If

3. 改用XMLHTTP直接获取页面源码（无需IE） #

既然右键查看页面源代码能看到参会者姓名，说明内容在初始HTML响应中，可以直接用XMLHTTP获取源码后解析，效率更高：

Public Sub GetAttendeesViaXMLHTTP()
    Dim xhr As Object
    Dim htmlDoc As Object
    Dim strURL As String
    
    strURL = "https://meetup.com/cflfreethought/events/301401141/attendees/"
    
    Set xhr = CreateObject("MSXML2.XMLHTTP.6.0")
    xhr.Open "GET", strURL, False
    xhr.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
    xhr.send
    
    If xhr.Status = 200 Then
        Set htmlDoc = CreateObject("HTMLFile")
        htmlDoc.write xhr.responseText
        htmlDoc.close
        
        ' 定位参会者容器并提取姓名
        Dim attendeesContainer As Object
        Set attendeesContainer = htmlDoc.querySelector(".attendee-list")
        If Not attendeesContainer Is Nothing Then
            Dim attendeeItem As Object
            For Each attendeeItem In attendeesContainer.getElementsByClassName("attendee-item")
                Debug.Print "参会者姓名:", attendeeItem.querySelector(".attendee-name").innerText
            Next
        End If
    Else
        Debug.Print "请求失败，状态码:" & xhr.Status
    End If
    
    Set xhr = Nothing
    Set htmlDoc = Nothing
End Sub

注意事项 #

• Meetup可能存在反爬机制，频繁请求可能被限制，建议添加请求间隔
• DOM元素的class或id可能随网站更新变化，需要根据实际页面源码调整选择器

内容的提问来源于stack exchange，提问作者Brooks