楼主
0Mouse |
Q：如何运用VBA提取以下网页的基金信息到Excel工作表中呢？- http://www.chinastock.com.cn/fund/fundscreening/index.shtml
A：代码如下：- Sub 运用VBA提取网页的基金信息到Excel工作表中()
- Dim IE As Object, srg$, arr, Ar, brr, i%, j%, sr$, Rst(1 To 10000, 1 To 13)
- Set IE = CreateObject("Microsoft.XMLHTTP")
- With IE
- .Open "get", "http://www.chinastock.com.cn/fund/fundscreening/index.shtml", False
- .send
- srg = .responsetext
- End With
- Cells.Clear
-
- '提取标题行文字
- arr = Replace(Split(Split(srg, "<THEAD>")(1), "</THEAD>")(0), "<br>", vbCrLf) '先后以<THEAD>和</THEAD>为拆分字符提取.responsetext中二者之间的内容，并将<br>替换成vbCrLf
- For i = 1 To 13
- Ar = Split(Replace(Split(arr, "</td>")(i + 2), "</span>", ""), ">")
- Rst(1, i) = Ar(UBound(Ar))
- Erase Ar
- Next
-
- '提取标题行以下内容
- brr = Split(srg, "jsp?symbol=") '以jsp?symbol=为拆分字符将.responsetext拆分成一个一维数组
- For i = 2 To UBound(brr) Step 2
- '提取基金代码
- Rst(i / 2 + 1, 1) = "'" & Split(brr(i), """")(0)
- '提取基金简称
- Rst(i / 2 + 1, 2) = Mid(Split(Split(brr(i), """")(3), "</a>")(0), 2)
- '提取净值日期
- Rst(i / 2 + 1, 3) = Split(Split(Split(brr(i), """")(3), "<td>")(1), "</td>")(0)
- '份额净值（元）
- Rst(i / 2 + 1, 4) = Val(Split(Split(Split(brr(i), """")(3), "</td>")(2), "<td>")(1))
- '份额净值（元）
- Rst(i / 2 + 1, 4) = Val(Split(Split(Split(brr(i), """")(3), "</td>")(2), "<td>")(1))
- '份额累计净值（元）
- Rst(i / 2 + 1, 5) = Val(Split(Split(Split(brr(i), """")(3), "</td>")(3), "<td>")(1))
- '份额累计净值（元）
- sr = Split(Split(Split(brr(i), """")(3), "</td>")(4), "<td>")(1)
- If sr Like "*--*" Then
- Rst(i / 2 + 1, 6) = "--"
- Else
- Rst(i / 2 + 1, 6) = Val(sr)
- End If
- sr = ""
- '提取当日(%)及以后的各列
- For j = 7 To 13
- Ar = Split(Split(Split(brr(i), """")(3), "</td>")(j - 2), ">")
- Rst(i / 2 + 1, j) = Val(Ar(UBound(Ar) - 1))
- Erase Ar
- Next
- Next
- '将结果数组Rst的数据写入工作表
- [A1].Resize(i / 2, 13) = Rst
- Columns.AutoFit
- End Sub
附件：
 运用VBA提取网页的基金信息到Excel工作表中.rar
|