目标格式见附件
Sub 大市行情()
Dim i, j, R, arr, reg As Object, mh, str As String, mark$
Application.ScreenUpdating = False
mark = Sheet3.Range("R1").Value '换行
Sheet3.UsedRange.Offset(2).ClearContents
Set reg = CreateObject("vbscript.regexp")
reg.Global = True
reg.Pattern = "{""f2"":""(.+?)"",""f3"":""(.+?)"",""f12"":""(.+?)"",""f14"":""(.+?)"",""f66"":""(.+?)"",""f72"":""(.+?)"",""f78"":""(.+?)"",""f84"":""(.+?)""}"
Set X = CreateObject("MSXML2.XMLHTTP")
With X
' For k = 1 To 271
.Open "GET", "https://push2.eastmoney.com/api/qt/clist/get?cb=jQuery112308664461492381323_1660644771097&fid=f62&po=1&pz=50&pn=1&np=1&fltt=2&invt=2&ut=b2884a393a59ad64002292a3e90d46a5&fs=m%3A0%2Bt%3A6%2Bf%3A!2,m%3A0%2Bt%3A13%2Bf%3A!2,m%3A0%2Bt%3A80%2Bf%3A!2,m%3A1%2Bt%3A2%2Bf%3A!2,m%3A1%2Bt%3A23%2Bf%3A!2&fields=f2,f3,f12,f14,f66,f72,f78,f84", False
.send
Do Until .readyState = 4 And .Status = 200
DoEvents
Loop
' str = StrConv(.responsebody, vbUnicode, &H804) '防乱码
str = .responsetext
str = Replace(str, mark, "") '处置响应数据中的换行
Debug.Print str
If reg.test(str) Then
Set mh = reg.Execute(str)
ReDim arr(1 To mh.Count, 1 To 8)
For i = 1 To mh.Count
arr(i, 1) = "=row()-2"
For j = 2 To UBound(arr, 2)
arr(i, j) = mh(i - 1).submatches(j - 2)
Next j
Next i
End If
With Sheet3
R = .Cells(Rows.Count, 1).End(3).Row
.Range("A" & R + 1).Resize(UBound(arr), UBound(arr, 2)) = arr
End With
' Next k
End With
Set X = Nothing
Set reg = Nothing
Application.ScreenUpdating = True
[A1:E1] = Split("不要序号列,代码,名称,现价,涨跌幅,超大单,大单,中单,小单") '不要序号列,f2,f3与f12,f14位置最好调一下
End Sub
|