excel - 从网页中的表中提取特定单元格

Question

我有一个子程序，可以很好地从网页中提取表格。我期待在 Excel 工作表中精确提取表格中的特定内部文本（第 3 列和第 5 行） 。

代码如下所述：

Sub Extracttable()

Dim oDom As Object: Set oDom = CreateObject("htmlFile")
Dim x As Long, y As Long
Dim oRow As Object, oCell As Object
Dim data

y = 1: x = 1

With CreateObject("msxml2.xmlhttp")
.Open "GET", "https://www.xe.com/currencytables/?from=USD&date=2020-02-07", False
.Send
oDom.Body.innerHTML = .responseText
End With

With oDom.getElementsByTagName("table")(0)
ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length)
For Each oRow In .Rows
For Each oCell In oRow.Cells
data(x, y) = oCell.innerText
y = y + 1
Next oCell
y = 1
x = x + 1
Next oRow
End With

Sheets(1).Cells(1, 1).Resize(UBound(data), UBound(data, 2)).Value = data
End Sub

如上所述，我需要提取一个特定的单元格。

任何与此相关的帮助将不胜感激。

标签： excelvbaweb-scrapinghtml-table

Answer 1

如果您的意思是您只想要单个值，那么：

Sub Extracttable()

Dim oDom As Object: Set oDom = CreateObject("htmlFile")
Dim x As Long, y As Long
Dim oRow As Object, oCell As Object
Dim data

y = 1: x = 1

With CreateObject("msxml2.xmlhttp")
.Open "GET", "https://www.xe.com/currencytables/?from=USD&date=2020-02-07", False
.send
oDom.body.innerHTML = .responseText
End With

Debug.Print oDom.getElementsByTagName("table")(0).Rows(5).Cells(2).innerText

End Sub

两者.Rows和.Cells都是从零开始的，所以我认为您对实际的第 6 行 > AUS 每美元的单位感兴趣。