VBA Table Scraping输出无分隔符问题求助

我编写了用于抓取表格并打印结果的VBA代码，预期输出格式如下：

County| Shropshire
Address| Adams Grammar School, High Street, Newport TF10 7BD
……

但实际得到的输出为：

CountyShropshire
AddressAdams Grammar School, High Street, Newport TF10 7BD
TypeBoys Grammar
Pupils799

标签与内容之间无分隔，请问我遗漏了什么？

代码如下：

Sub scrapeschools()

Dim ie As New SHDocVw.InternetExplorer
Dim htmldoc As MSHTML.HTMLDocument

Dim htmltable As MSHTML.htmltable

Dim tablerow As MSHTML.IHTMLElement
Dim tablecell As MSHTML.IHTMLElement
Dim tablecol As MSHTML.IHTMLElement

ie.Visible = True

ie.navigate "https://www.11plusguide.com/grammar-school-test-areas/wolverhampton-shropshire-walsall/adams-grammar-school/"

Do While ie.readyState < READYSTATE_COMPLETE Or ie.Busy
Loop

Set htmldoc = ie.document

Set htmltable = htmldoc.getElementsByTagName("table")(0)

    For Each tablerow In htmltable.Children
    
            For Each tablecell In tablerow.Children
                Debug.Print tablecell.innerText
            Next tablecell
   
    Next tablerow

End Sub

解决方案 #

问题出在你遍历单元格时直接逐个打印内容，没有将同一行的标签单元格和内容单元格用分隔符拼接。目标表格的结构是每行包含两个单元格：左侧为标签、右侧为对应内容，需要将这两个单元格的内容用| 连接后再打印。

同时，原代码中htmltable.Children指向的是表格的<tbody>元素，直接遍历htmltable.Rows可以更准确地获取表格的每一行。

修改后的代码如下：

Sub scrapeschools()

Dim ie As New SHDocVw.InternetExplorer
Dim htmldoc As MSHTML.HTMLDocument
Dim htmltable As MSHTML.htmltable
Dim tablerow As MSHTML.HTMLTableRow

ie.Visible = True
ie.navigate "https://www.11plusguide.com/grammar-school-test-areas/wolverhampton-shropshire-walsall/adams-grammar-school/"

Do While ie.readyState < READYSTATE_COMPLETE Or ie.Busy
Loop

Set htmldoc = ie.document
Set htmltable = htmldoc.getElementsByTagName("table")(0)

' 遍历表格的每一行
For Each tablerow In htmltable.Rows
    ' 确保每行有两个单元格（标签+内容）
    If tablerow.Cells.Count = 2 Then
        ' 拼接标签、分隔符和内容后打印
        Debug.Print tablerow.Cells(0).innerText & "| " & tablerow.Cells(1).innerText
    End If
Next tablerow

' 关闭IE（可选，避免进程残留）
ie.Quit
Set ie = Nothing

End Sub

关键修改说明： #

• 将tablerow的类型改为MSHTML.HTMLTableRow，更贴合表格行的对象类型
• 直接遍历htmltable.Rows获取所有表格行，避免层级遍历的冗余
• 判断每行单元格数量为2时，将两个单元格内容用| 拼接后打印，实现预期格式
• 增加了IE的关闭和对象释放代码，避免浏览器进程残留

内容的提问来源于stack exchange，提问作者Suren Grigoryan