如何使用VBA定向爬取ECHA注册档案中各暴露途径的DNEL数据

核心解决思路 #

• 放弃全局检索HorDL类元素的方案，改为先定位每个暴露途径的标题节点，再从该节点的下游关联区域提取对应DNEL数据，从根源上避免匹配到同暴露途径下的其他无关HorDL区块
• 提前整理所有目标暴露途径的DOM节点ID，遍历抓取保证输出顺序和需求完全匹配

修改后可实现完整抓取的VBA代码 #

Public Sub GetAllDNEL()
    Dim XMLReq As New MSXML2.XMLHTTP60
    Dim HTMLDoc As New MSHTML.HTMLDocument
    Dim routeTitle As MSHTML.HTMLGenericElement
    Dim dataBlock As MSHTML.HTMLGenericElement
    Dim routeIds, i As Long, routeName As String, dnelType As String, dnelValue As String
    
    ' 预定义所有需要提取的暴露途径对应的DOM ID
    routeIds = Array("sWorkersHazardViaInhalationRoute", _
                    "sWorkersHazardViaDermalRoute", _
                    "sGeneralPopulationHazardViaInhalationRoute", _
                    "sGeneralPopulationHazardViaDermalRoute", _
                    "sGeneralPopulationHazardViaOralRoute")
    
    ' 页面请求逻辑和原有代码一致
    XMLReq.Open "Get", "https://echa.europa.eu/registration-dossier/-/registered-dossier/16016/7/1", False
    XMLReq.send
    If XMLReq.Status <> 200 Then
        MsgBox "请求出错" & vbNewLine & XMLReq.Status & " - " & XMLReq.statusText
        Exit Sub
    End If
    HTMLDoc.body.innerHTML = XMLReq.responseText
    
    ' 遍历每个暴露途径抓取数据
    For i = LBound(routeIds) To UBound(routeIds)
        ' 定位当前暴露途径标题
        Set routeTitle = HTMLDoc.getElementById(routeIds(i))
        If Not routeTitle Is Nothing Then
            routeName = routeTitle.innerText
            ' 定位标题下方对应的首个DNEL数据块
            Set dataBlock = routeTitle.NextSibling.getElementsByClassName("HorDL")(0)
            If Not dataBlock Is Nothing Then
                dnelType = dataBlock.getElementsByTagName("dd")(0).innerText
                dnelValue = dataBlock.getElementsByTagName("dd")(1).innerText
                ' 按要求格式输出
                Debug.Print routeName & ", " & dnelType & ", " & dnelValue
            End If
        End If
    Next i
End Sub

代码说明 #

1. 预定义的routeIds数组是根据ECHA注册档案页的DOM规则整理的所有DNEL暴露途径对应的节点ID，可适配同类型其他注册档案的抓取
2. 每个标题节点的NextSibling就是对应暴露途径的内容容器，取容器内第一个HorDL元素即为DNEL核心数据区块，完全不会匹配到其他无关数据
3. 直接输出逗号分隔的目标格式，可直接写入Excel单元格，不需要额外做数据清洗

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