VBA-简单抓取网络数据

1.在Excel中如果能够直接获取网上的数据，是非常便利的，于是在这里以获取标题和链接为例，来介绍一下用VBA实现网络抓取

效果如下图所示

2.如何实现，具体代码和解释如下

Option Explicit
Public Sub getlist()
'将单元格内容清空
[a:b].ClearContents
'定义网址
    Dim strurl As String
    strurl = "http://www.baidu.com/s?wd=你好"
'创建对象HTML
    Dim ht As Object
    Set ht = CreateObject("MSXML2.XMLHTTP")
    With ht
'发送请求
     .Open "get", strurl, False
     .send
'等待响应
     Do While .readyState <> 4
         DoEvents
      Loop
'定义数组来接收结果
    Dim s() As String
    s = Split(.responseText, "<h3 class=""t") '得到网站的源码以"h3 class=t"为关键字
   Dim arr() '定义一个二维数组来接收想要的内容
   ReDim arr(0 To UBound(s), 0 To 1)
   arr(0, 0) = "标题"
   arr(0, 1) = "链接"
   Dim i As Long
   For i = 1 To UBound(s)
   '获取标题
    arr(i, 0) = Replace(Replace(Replace(Split(Split(s(i), "</a>")(0), "target=""_blank""")(1), Chr(10), ""), "<em>", ""), "</em>", "")
    '获取链接
    arr(i, 1) = Split(Split(s(i), "href=""")(1), """")(0)
    
    
   Next
    
    End With
    '将内容保存在表格中
    [a1:b1].Resize(UBound(arr)) = arr
    '去掉文本中的空格
    For i = 1 To UBound(s)
     Range("A" & i).Select
       Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Next
 
End Sub