|
- Sub 抓取数据()
- Dim str$, URL$, s1$, s2$, s3$, i%, arr, brr, xHttp As Object
- If ([N4] > [N3] Or [N4] < 1) Then
- MsgBox ("输入有误!")
- Exit Sub
- End If
- For i = (Range("N1").Value + 1) To (Range("N4").Value + Range("N1").Value) '最大 3118页
- URL = "http://sthjt.ah.gov.cn/pages/SJZX_List.aspx?CityCode=340100&LX=4&KSSJ=2017-01-01%2000&JSSJ=2017-12-31%2023&page=" & i
- s1 = " widtd=""80px"" algin=""center"""
- s2 = " widtd=""60px"" algin=""center"""
- s3 = " algin=""center"" height=""26px"""
- Set xHttp = CreateObject("Microsoft.XMLHTTP")
- xHttp.Open "GET", URL, False
- xHttp.send
- str = xHttp.responseText
- str = Replace(Replace(Replace(Split(Split(str, "</table>")(0), "主要污染物")(1), s1, ""), s2, ""), s3, "")
- arr = Split(str, "<tr>")
- ReDim brr(1 To UBound(arr), 1 To 11)
- For j = 1 To UBound(arr)
- For k = 1 To 11
- brr(j, k) = Trim(Replace(Replace(Replace(Replace(Replace(Split(arr(j), "<td>")(k), "</td>", ""), vbCr, ""), vbLf, ""), vbCrLf, ""), "</tr>", ""))
- Next k
- Next j
- Range("A1000000").End(3).Offset(1, 0).Resize(UBound(arr), 11) = brr
- Next i
- [N1] = i - 1
- End Sub
复制代码
不会用正则表达式,所以用了很繁杂的字符串函数去处理
抓取数据 - 副本.zip
(19.31 KB, 下载次数: 3)
|
|