tblExchangeRate如下图:
通过下面的函数即可为表中每个货币获取到对美元的汇率(如果存在):
Public Function GetUSDRate()
On Error Resume Next
Dim HTML As Object
Dim URL As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblExchangeRate")
Set HTML = CreateObject("htmlfile")
With CreateObject("msxml2.xmlhttp")
Do Until rst.EOF
rst.Edit
URL = "http://www.zhaotool.com/v1/api/huobi/e10adc3949ba59abbe56e057f20f883e/" & rst!tocurrency & "/USD"
.Open "get", URL, False
.send
HTML.BODY.innerhtml = StrConv(.responsebody, vbUnicode)
If Mid(HTML.BODY.innerhtml, 10, 1) = "0" Then
rst![Rate] = Int(Mid(Split(HTML.BODY.innerText, "rate")(1), 4, Len(Split(HTML.BODY.innerText, "rate")(1)) - 6) * 10) / 10
rst.Update
End If
rst.MoveNext
Loop
rst.Close
End With
Set HTML = Nothing
End Function