Edge
Chrome
Firefox
2022-01-21 16:12 根据新浪接口更新了代码，再修复一次
打开VBA编辑器WinHttp.XMLHTTP 替换 成 WinHttp.WinHttpRequest.5.1
在 .send 前面加 .setRequestHeader "Referer", "http://finance.sina.com.cn/"
例如我这个用的这个函数原先是：
With CreateObject("WinHttp.XMLHTTP")
.Open "GET", url, False
.Send
sTemp = .responseText
End With改成下面的就正常了
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", url, False
.setRequestHeader "Referer", "http://finance.sina.com.cn/"
.Send
sTemp = .responseText
End With==============
获取新浪行情的完整函数
Sub GetNetValueDetail(ByVal sheet As Worksheet, beginCol As String) '基金查询
Dim rowCount As Integer
Dim url As String
Dim sTemp As String
rowCount = sheet.Range("A65535").End(xlUp).Row '获取行数
url = "http://hq.sinajs.cn/list=" '新浪行情数据接口
For i = 2 To rowCount '从第二行开始，第一列为股票代码
code = sheet.Range("A" & i).Text
If Len(code) < 6 Then
code = "unknow"
Else
code = "of" & Right(code, 6) '基金代码前of(open fund)
End If
If i = 2 Then
url = url & code
Else
url = url & "," & code
End If
Next i
'获取新浪股票行情数据，放入sTemp变量
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", url, False
.setRequestHeader "Referer", "http://finance.sina.com.cn/"
.Send
sTemp = .responseText
End With
splits = Split(sTemp, ";")
For i = 0 To rowCount - 1
mystr = splits(i)
ss = InStr(mystr, ",")
If ss > 1 Then
startindex = InStr(1, mystr, """")
endindex = InStrRev(mystr, """")
substr = Mid(mystr, startindex + 1, endindex - startindex - 1) '引号中的有效数据
valuearray = Split(substr, ",")
begin = Asc(beginCol)
J = 0
sheet.Range(Chr(begin + J) & i + 2).Value = valuearray(0) '名称
J = J + 1
sheet.Range(Chr(begin + J) & i + 2).Value = valuearray(1) '净值
J = J + 1
sheet.Range(Chr(begin + J) & i + 2).Value = valuearray(2) '累计净值
J = J + 1
sheet.Range(Chr(begin + J) & i + 2).Value = valuearray(3) '上日净值
J = J + 1
sheet.Range(Chr(begin + J) & i + 2).Value = Format(valuearray(4) / 100, "0.00%") '净值涨跌幅
sheet.Range(Chr(begin + J) & i + 2).Font.Color = GetFontColor(valuearray(1) - valuearray(3))
J = J + 1
sheet.Range(Chr(begin + J) & i + 2).Value = valuearray(5) '日期
End If
Next i
End Sub
0
不同懂语言，这个认证语句是要放在什么位置
strUrl = "http://hq.sinajs.cn/list=" & Code(i) & Cells(1 + i, 1) '起始代码单元格
xmlobject.Open "GET", strUrl, False
xmlobject.setRequestHeader " Referer", "https://finance.sina.com.cn"
xmlobject.send
If xmlobject.readystate = 4 Then
strReturn = xmlobject.responsetext
intLen = Len(strReturn) - 25 '剔除无关数据
strReturn = Mid(strReturn, 22, intLen)
arry = Split(strReturn, ",") '按逗号分隔数据，放入数组arry
intLenA = UBound(arry) - LBound(arry) + 1 '数组长度，此处未使用，可结合For遍历arry
'获取目标数据
Cells(1 + i, 3) = arry(3) '现值
Cells(1 + i, 4) = arry(3) - arry(2) '幅度差
Cells(1 + i, 5) = Round((arry(3) - arry(2)) / arry(2), 4) '幅度百分比
Cells(1 + i, 6) = arry(8) / 100 '量
Cells(1 + i, 2) = arry(0)
End If
Next i
strUrl = "http://hq.sinajs.cn/list=" & Code(i) & Cells(1 + i, 1) '起始代码单元格
xmlobject.Open "GET", strUrl, False
xmlobject.setRequestHeader " Referer", "https://finance.sina.com.cn"
xmlobject.send
If xmlobject.readystate = 4 Then
strReturn = xmlobject.responsetext
intLen = Len(strReturn) - 25 '剔除无关数据
strReturn = Mid(strReturn, 22, intLen)
arry = Split(strReturn, ",") '按逗号分隔数据，放入数组arry
intLenA = UBound(arry) - LBound(arry) + 1 '数组长度，此处未使用，可结合For遍历arry
'获取目标数据
Cells(1 + i, 3) = arry(3) '现值
Cells(1 + i, 4) = arry(3) - arry(2) '幅度差
Cells(1 + i, 5) = Round((arry(3) - arry(2)) / arry(2), 4) '幅度百分比
Cells(1 + i, 6) = arry(8) / 100 '量
Cells(1 + i, 2) = arry(0)
End If
Next i
京公网安备 11010802031449号