从一堆网址中提取数据[关闭]

1 回答

• -1

  主要子将获得A列中每个URL的评级和评论数量，并将它们放在B列和C列中 . 我希望这对您有所帮助 .

  Sub main()
  Dim l As Long
  l = Cells(Rows.Count, 1).End(xlUp).Row
  For i = 2 To l
  test Range("A" & i)
  Next
  End Sub
  
  Sub test(URL As Range)
      my_url = URL.Value
      Set html_doc = CreateObject("htmlfile")
      Set xml_obj = CreateObject("MSXML2.XMLHTTP")
  
      xml_obj.Open "GET", my_url, False
      xml_obj.send
      html_doc.body.innerhtml = xml_obj.responseText
      Set xml_obj = Nothing
  
      Set Results = html_doc.body.getElementsByTagName("i")
  
      For Each itm In Results
  
          If InStr(1, itm.outerhtml, "star-img", vbTextCompare) > 0 Then
              numb_stars = itm.getAttribute("title")
              Exit For
          Else
          End If
      Next
      Set Results = html_doc.body.getElementsByTagName("span")
      For Each itm In Results
          If InStr(1, itm.outerhtml, "reviewCount", vbTextCompare) > 0 Then
              numb_rev = itm.innertext
              Exit For
          Else
          End If
      Next
  
      URL.Offset(0, 1) = numb_stars
       URL.Offset(0, 2) = numb_rev
  End Sub
  

  预览我的输出：

  

  回复于 2024-05-09T07:47:58+08:00