将网页class元素转为Excel表格问题：内容全粘贴至单个单元格

我尝试将网页中class为niwa-table regionalIndices的表格提取到Excel，但当前代码仅把所有内容粘贴到A1单个单元格，无法生成和网页格式一致的表格。问题出在这行代码：

Sheet1.Range("A1").Value = objIE.document.getElementsByClassName("niwa-table regionalIndices")(0).innerText

原VBA代码如下：

Sub ExtractLastValue()

Dim objIE As Object
Set objIE = CreateObject("InternetExplorer.Application")

objIE.Visible = True

objIE.navigate ("https://fireweather.niwa.co.nz/region/Otago")

Dim t As Date, ele As Object
Const MAX_WAIT_SEC As Long = 8 '<==Adjust wait time

While objIE.Busy Or objIE.readyState < 4: DoEvents: Wend
t = Timer
Do
    DoEvents
    On Error Resume Next
    Set ele = objIE.document.getElementById("")
    If Timer - t > MAX_WAIT_SEC Then Exit Do
    On Error GoTo 0
Loop While ele Is Nothing

If Not ele Is Nothing Then
    'do something
End If
Sheet1.Range("A1").Value = objIE.document.getElementsByClassName("niwa-table regionalIndices")(0).innerText

End Sub

直接赋值表格的innerText会把所有内容合并成文本块，要生成对应格式的表格，需要遍历表格的行和单元格，逐个写入Excel：

修改后的完整代码：

Sub ExtractLastValue()
    Dim objIE As Object
    Set objIE = CreateObject("InternetExplorer.Application")
    
    objIE.Visible = True
    objIE.navigate "https://fireweather.niwa.co.nz/region/Otago"
    
    Dim t As Double, tableObj As Object
    Const MAX_WAIT_SEC As Long = 8 '<==调整等待时间
    
    ' 等待页面加载完成
    While objIE.Busy Or objIE.readyState < 4: DoEvents: Wend
    
    ' 等待目标表格加载
    t = Timer
    Do
        DoEvents
        On Error Resume Next
        Set tableObj = objIE.document.getElementsByClassName("niwa-table regionalIndices")(0)
        If Timer - t > MAX_WAIT_SEC Then Exit Do
        On Error GoTo 0
    Loop While tableObj Is Nothing
    
    If Not tableObj Is Nothing Then
        Dim rowNum As Long, colNum As Long
        Dim tableRow As Object, tableCell As Object
        
        rowNum = 1
        ' 遍历表格每一行
        For Each tableRow In tableObj.Rows
            colNum = 1
            ' 遍历当前行的每个单元格
            For Each tableCell In tableRow.Cells
                Sheet1.Cells(rowNum, colNum).Value = tableCell.innerText
                colNum = colNum + 1
            Next tableCell
            rowNum = rowNum + 1
        Next tableRow
    End If
    
    ' 关闭IE并释放资源
    objIE.Quit
    Set objIE = Nothing
End Sub

关键修改点 #

• 替换了原代码中无效的等待逻辑：原代码getElementById("")无法定位任何元素，改为等待目标表格加载完成
• 放弃直接赋值innerText，改为逐行逐单元格遍历表格，将每个单元格内容对应写入Excel的单元格
• 添加了IE对象的关闭和资源释放逻辑，避免内存占用

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