第3步：当你点击 “参考资料 “时，将打开一个新的窗口，如下图所示。从可用的引用列表中勾选Microsoft HTML Object Library和Microsoft Internet Controls的方框。当你在模块中引用这两个文件时，它将有助于在你设置Microsoft Edge时打开它。一旦你完成了复选，点击确定。
Dim tbl As HTMLTable
Set tbl = ie.document.getElementById("myTable")
At this point, we are ready to send that table data to our worksheet.
Next, we would set two variables that would represent our worksheet column and row.
代码
Dim rowCounter As Integer
Dim colCounter As Integer
rowCounter = 1
colCounter = 1
Dim mysh As Worksheet
Set mysh = ThisWorkbook.Sheets("Web Scraping")
最后，输入下面几行代码来访问表格的内容。
代码
For Each tr In tbl.getElementsByTagName("tr")
'Loop through table header.
For Each th In tr.getElementsByTagName("th")
mysh.Cells(rowCounter, colCounter).Value = th.innerText
colCounter = colCounter + 1
Next th
'Loop through table cells.
For Each td In tr.getElementsByTagName("td")
mysh.Cells(rowCounter, colCounter).Value = td.innerText
colCounter = colCounter + 1
Next td
colCounter = 1
rowCounter = rowCounter + 1
Next tr
The complete VBA code would look like this:
Sub VBA_Web_Scraping()
Dim ie As Object
Dim url As String
url = "https://www.iban.com/country-codes"
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate url
Do While ie.Busy: DoEvents: Loop
Do Until ie.readyState = 4: DoEvents: Loop
'Declaring the table elements.
Dim tbl As HTMLTable
Set tbl = ie.document.getElementById("myTable")
'Declaring variables for our worksheet column and roll.
Dim rowCounter As Integer
Dim colCounter As Integer
rowCounter = 1
colCounter = 1
'Dclaring the table elements again.
Dim tr As HTMLTableRow
Dim td As HTMLTableCell
Dim th
Dim mysh As Worksheet
Set mysh = ThisWorkbook.Sheets("VBA_Web_Scraping")
For Each tr In tbl.getElementsByTagName("tr")
'Loop through table header.
For Each th In tr.getElementsByTagName("th")
mysh.Cells(rowCounter, colCounter).Value = th.innerText
colCounter = colCounter + 1
Next th
'Loop through table cells.
For Each td In tr.getElementsByTagName("td")
mysh.Cells(rowCounter, colCounter).Value = td.innerText
colCounter = colCounter + 1
Next td
colCounter = 1
rowCounter = rowCounter + 1
Next tr
End Sub
Sub VBA_Web_Scraping()
Dim tdl As New WebDriver
'Declaring variables for worksheet column and roll.
Dim rowCounter As Integer
Dim colCounter As Integer
rowCounter = 1
colCounter = 1
Application.ScreenUpdating = False
tdl.Start "chrome"
tdl.Get " https://www.iban.com/country-codes "
For Each th In tdl.FindElementById("myTable").FindElementByTag("thead").FindElementsByTag("tr")
colCounter = 1
For Each t In th.FindElementsByTag("th")
Sheet2.Cells(1, colCounter).Value = t.Text
colCounter = colCounter + 1
Next t
Next th
For Each tr In tdl.FindElementById("myTable").FindElementByTag("tbody").FindElementsByTag("tr")
colCounter = 1
For Each td In tr.FindElementsByTag("td")
Sheet2.Cells(rowCounter, colCounter).Value = td.Text
colCounter = colCounter + 1
Next td
rowCounter = rowCounter + 1
Next tr
Application.Wait Now + TimeValue("00:00:20")
End Sub
