vbs读取html td,[VBS脚本] Excel自动获取基金出错

[Visual Basic] 纯文本查看 复制代码Sub 获取涨幅()

Sheets(1).Range("S300").Formula = "=MATCH(" & """####""" & ",B:B,0)-1"

endRow = Sheets(1).Range("S300").Value

Sheets(1).Range("S300").ClearContents

For i = 2 To endRow

If Sheets(1).Range("B" & i).Text <> "" Then

Sheets(1).Range("F" & i) = "正在加载..."

Sheets(1).Range("H" & i) = "正在加载..."

Sheets(1).Range("K" & i) = "正在加载..."

Set HTML = CreateObject("htmlfile")

Set http = CreateObject("Msxml2.ServerXMLHTTP")

Value = Sheets(1).Range("B" & i).Text

HTML.designMode = "on"

http.Open "GET", "http://fund.eastmoney.com/" & Value & ".html", False

http.send

strHtml = http.responseText

If strHtml <> "" Then

HTML.write strHtml

Set trs = HTML.getElementById("gz_gszzl")

Set gz_gsz = HTML.getElementById("gz_gsz")

If Sheets(1).Range("A" & i).Value = "" Then

Title = HTML.getElementsByTagName("title")(0).innerText

Title = Left(Title, InStrRev(Title, "(") - 1)

Sheets(1).Range("A" & i) = Title

End If

If TypeName(trs) <> "Nothing" And TypeName(gz_gsz) <> "Nothing" Then

Sheets(1).Range("K" & i) = gz_gsz.innerText

' 累计收益 (含预估)

gsz = gz_gsz.innerText

If gsz = "--" Then

gsz = "0"

End If

Sheets(1).Range("H" & i) = (gsz - Sheets(1).Range("D" & i).Value) * Sheets(1).Range("C" & i).Value

' 今日估值

' Debug.Print (trs.innerText)

If trs.innerText = "0.00%" Then

Sheets(1).Range("F" & i) = "'" & trs.innerText

Else

Sheets(1).Range("F" & i) = trs.innerText

End If

Call 获取风险指标(Value, i)

Else

Sheets(1).Range("F" & i) = "不支持该基金"

Sheets(1).Range("H" & i) = "不支持该基金"

Sheets(1).Range("K" & i) = "不支持该基金"

End If

Else

Sheets(1).Range("F" & i) = "获取出错，请检查基金代码后重试"

Sheets(1).Range("H" & i) = "获取出错，请检查基金代码后重试"

Sheets(1).Range("K" & i) = "获取出错，请检查基金代码后重试"

End If

End If

Next

End Sub

Sub 获取风险指标(code, i)

Sheets(1).Range("J" & i) = "正在加载..."

Set HTML = CreateObject("htmlfile")

Set http = CreateObject("Msxml2.ServerXMLHTTP")

HTML.designMode = "on"

http.Open "GET", "http://fundf10.eastmoney.com/tsdata_" & code & ".html", False

http.send

strHtml = http.responseText

HTML.write strHtml

Set td = HTML.getElementsByTagName("table")(1).getElementsByTagName("tr")(2).getElementsByTagName("td")

y = Sheets(1).Range("J1").Value

y1 = CStr(td(1).innerText)

y2 = CStr(td(2).innerText)

y3 = CStr(td(3).innerText)

Sheets(1).Range("J" & i).Formula = "=IF(INDEX(J1,1)=" & """夏普比率(1年)""" & "," & Chr(34) & y1 & Chr(34) & ",IF(INDEX(J1,1)=" & """夏普比率(2年)""" & "," & Chr(34) & y2 & Chr(34) & "," & Chr(34) & y3 & Chr(34) & "))"

End Sub