如何用Excel VBA爬取Speedhive圈速数据并整理为规范成绩表

我需要从Speedhive赛事计时网站抓取比赛圈速数据导入Excel，用于赛后发布成绩。目前已经可以成功获取页面文本数据，但因为该网站没有使用HTML的table标签展示圈速，抓取到的内容没办法直接按需求排列成规范的成绩表。我已经完成初步代码编写，可以通过msgbox输出抓取到的原始数据，但不清楚如何按照指定规则把数据整理为目标表结构，目标抓取地址为：https://speedhive.mylaps.com/LiveTiming/OVXRNRVR-2147485174/Active

已完成的各版本VBA代码 #

版本1 初步抓取代码 #

Sub Get_Race_Results()

Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim times As Variant

website = "https://speedhive.mylaps.com/LiveTiming/OVXRNRVR-2147485174/Active"
Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", website, False
request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
request.send
response = StrConv(request.responseBody, vbUnicode)
html.body.innerHTML = response
times = html.getElementsByClassName("row tab-pane active scrollable").Item(0).innerText

MsgBox times
End Sub

版本V1 修订代码 #

Sub Get_Race_Results()
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim times As Variant
website = "https://speedhive.mylaps.com/Sessions/6333683"
Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", website, False
request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
request.send
response = StrConv(request.responseBody, vbUnicode)
html.body.innerHTML = response
times = html.getElementsByClassName("tab-content").Item(0).innerText
Dim LookInHere As String
Dim Counter As Integer
Dim SplitCatcher As Variant
LookInHere = times
SplitCatcher = Split(LookInHere, vbLf)
'headers
Range("A1").Value = Split(LookInHere, vbLf)(0)
Range("B1").Value = Split(LookInHere, vbLf)(1)
Range("C1").Value = Split(LookInHere, vbLf)(2)
Range("D1").Value = Split(LookInHere, vbLf)(3)
Range("E1").Value = Split(LookInHere, vbLf)(4)
Range("F1").Value = Split(LookInHere, vbLf)(5)
Range("G1").Value = Split(LookInHere, vbLf)(6)
Range("H1").Value = Split(LookInHere, vbLf)(7)
Range("I1").Value = Split(LookInHere, vbLf)(8)
Range("J1").Value = Split(LookInHere, vbLf)(9)
'1line
Range("A2").Value = Split(LookInHere, vbLf)(10)
Range("B2").Value = Split(LookInHere, vbLf)(11)
'IGNORE C2 OR LEAVE IT BLANK
Range("D2").Value = Split(LookInHere, vbLf)(12)
Range("E2").Value = Split(LookInHere, vbLf)(13)
Range("F2").Value = Split(LookInHere, vbLf)(14)
Range("G2").Value = Split(LookInHere, vbLf)(15)
Range("H2").Value = Split(LookInHere, vbLf)(16)
Range("I2").Value = Split(LookInHere, vbLf)(17)
Range("J2").Value = Split(LookInHere, vbLf)(18)
'2line etc etc until end

 End Sub

版本V2 修订代码 #

Sub Get_Web_Data()
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim times As Variant
website = "https://speedhive.mylaps.com/Sessions/6333683"
Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", website, False
request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
request.send
response = StrConv(request.responseBody, vbUnicode)
html.body.innerHTML = response
times = html.getElementsByClassName("tab-content").Item(0).innerText

Dim LookInHere As String
Dim Counter As Integer
Dim SplitCatcher As Variant
LookInHere = times
SplitCatcher = Split(LookInHere, vbLf)
Dim lrow As Long
Dim col As Long
Dim i As Long

For col = 2 To 12
For lrow = 1 To 9
Worksheets("Sheet1").Cells(col, lrow).Value = Split(LookInHere, vbLf)((10) + i)
i = i + 1
Next lrow
Next col
End Sub

内容的提问来源于stack exchange，提问作者Joao Moreira