VBA 提高篇37 VBA编写网络爬虫_5步走

抽取网页数据的关键:

1) 获取网页字符传并保存到VBA变量中
2) 使用正则表达式等方法处理该字符串

VBA网抓基本步骤:

    Dim s As String, xhttp As Object
     
    Set xhttp = CreateObject("Microsoft.XMLHTTP")
    
    xhttp.Open "GET", "http://www.boc.cn/sourcedb/whpj/", False
    
    '将抓取到的网页字符传保存到变量 s 中,然后交个getRate 子过程处理
    s = xhttp.responsetext
    
    getRate s
    
	End Sub
	'本子过程是第29回作业的参考答案,能够接收一个字符串参数
	'然后将使用正则表达式分析字符串,并将捕获组内容存入工作表
	Sub getRate(s As String)
 '-------------------------------------------------------------'   
    Dim reg As Object, m As Object, mchs As Object
    Dim i As Long, j As Long, p As String
    
    Set reg = CreateObject("vbscript.regexp")
    
    p = ""
    
    reg.Pattern = p
    reg.golbal = True
    
    Set mchs = reg.Execute(s)
    
    i = 2

    For Each m In mchs
        For j = 0 To m.submatches.Count - 1
            Cells(i, j + 1) = m.submatches.Item(j)
        Next j
        
        i = i + 1
    
    Next m

End Sub