Zillow房源VBA爬虫问题求助：数据格式化与翻页异常

Let's tackle each of your issues step by step, with clear explanations and a revised code that checks off all your requirements:

1. Problem: Raw Combined Text in Single Cells #

Root Cause #

You’re dumping the full innerText of each property card into one cell, which mixes all details (address, beds, baths, etc.) into a messy block. Batch text-to-columns fails because the data structure isn’t consistent enough for reliable splitting.

Solution #

Parse individual property details directly from their specific HTML elements, then write each piece to its own column. This eliminates the need for post-processing entirely.

2. Problem: Broken Pagination (Next Page Issues) #

Root Cause #

• Zpage.Click doesn’t work with MSXML2.XMLHTTP—it’s a headless HTTP client, not a browser, so clicking elements won’t trigger new page requests.
• Your Exit For was misplaced, so the loop stopped after checking the first <a> tag, never finding the actual "Next page" link.
• Zillow’s pagination links are nested in a dedicated container, not scattered across all <a> tags.

Solution #

Extract the href of the "Next page" link, then loop through pages by sending new HTTP requests to each subsequent URL until no next page exists.

3. Problem: Null Value for Property Links #

Root Cause #

You’re targeting the wrong element. The property URL lives in the parent <a> tag of the list-card-info element, not the list-card-info itself.

Solution #

Grab the href attribute from the parent anchor tag wrapping each property card.

4. Problem: Dynamic URL from Sheet1 Throws Errors #

Root Cause #

You likely didn’t properly qualify the worksheet range reference, or tried to use a range directly in the HTTP request without storing it in a variable first.

Solution #

Store the URL from Sheet1 in a string variable, add validation to ensure it’s not empty, and use that variable in your request.

Revised Full Code #

Option Explicit

Sub GetZillowSold()
    Dim XMLReq As New MSXML2.XMLHTTP60
    Dim HTMLDoc As New MSHTML.HTMLDocument
    Dim ListCards As MSHTML.IHTMLElementCollection
    Dim InfoCard As MSHTML.IHTMLElement
    Dim NextPageLink As MSHTML.IHTMLElement
    Dim rowNum As Integer
    Dim baseURL As String
    Dim currentURL As String
    
    ' --- Dynamic URL from Sheet1 ---
    baseURL = Trim(Worksheets("Sheet1").Range("A1").Value)
    If baseURL = "" Then
        MsgBox "Please enter a valid Zillow URL in Sheet1 cell A1.", vbExclamation
        Exit Sub
    End If
    currentURL = baseURL
    
    ' Add new worksheet and set headers
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets.Add
    ws.Range("A1:G1").Value = Array("Address", "Price", "Bedroom", "Bath", "Sqft", "Date Sold", "Property Link")
    rowNum = 2
    
    Do
        ' Send HTTP request with User-Agent to avoid blocking
        Set XMLReq = New MSXML2.XMLHTTP60
        XMLReq.Open "GET", currentURL, False
        XMLReq.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
        XMLReq.send
        
        If XMLReq.Status <> 200 Then
            MsgBox "Failed to load page. Status: " & XMLReq.Status & " - " & XMLReq.statusText
            Exit Sub
        End If
        
        ' Load response into HTML document
        HTMLDoc.body.innerHTML = XMLReq.responseText
        Set XMLReq = Nothing
        
        ' Get all property cards
        Set ListCards = HTMLDoc.getElementsByClassName("list-card-info")
        
        ' Process each card
        For Each InfoCard In ListCards
            Dim cardAnchor As MSHTML.IHTMLElement
            Set cardAnchor = InfoCard.parentElement ' The parent <a> tag holds the property link
            
            ' --- Extract individual details ---
            ' Address
            ws.Cells(rowNum, 1).Value = InfoCard.getElementsByClassName("list-card-addr")(0).innerText
            ' Price
            ws.Cells(rowNum, 2).Value = InfoCard.getElementsByClassName("list-card-price")(0).innerText
            ' Beds
            On Error Resume Next ' Handle cases where beds/baths/sqft might be missing
            ws.Cells(rowNum, 3).Value = Split(InfoCard.getElementsByClassName("list-card-details")(0).children(0).innerText, " ")(0)
            ' Baths
            ws.Cells(rowNum, 4).Value = Split(InfoCard.getElementsByClassName("list-card-details")(0).children(1).innerText, " ")(0)
            ' Sqft
            ws.Cells(rowNum, 5).Value = Split(InfoCard.getElementsByClassName("list-card-details")(0).children(2).innerText, " ")(0)
            On Error GoTo 0
            ' Sold Date (from list-card-top)
            ws.Cells(rowNum, 6).Value = Mid(InfoCard.parentElement.getElementsByClassName("list-card-top")(0).innerText, 6)
            ' Property Link
            ws.Cells(rowNum, 7).Value = "https://www.zillow.com" & cardAnchor.getAttribute("href")
            
            rowNum = rowNum + 1
        Next InfoCard
        
        ' --- Find Next Page Link ---
        Set NextPageLink = Nothing
        Dim pageLinks As MSHTML.IHTMLElementCollection
        On Error Resume Next
        Set pageLinks = HTMLDoc.getElementsByClassName("pagination")(0).getElementsByTagName("a")
        On Error GoTo 0
        
        For Each NextPageLink In pageLinks
            If NextPageLink.getAttribute("title") = "Next page" Then
                currentURL = "https://www.zillow.com" & NextPageLink.getAttribute("href")
                Exit For
            End If
        Next NextPageLink
        
        ' Exit loop if no next page exists
        If NextPageLink Is Nothing Then Exit Do
        
        ' Add delay to avoid overwhelming Zillow's servers (optional but recommended)
        Application.Wait Now + TimeValue("00:00:02")
    Loop
    
    ' Auto-fit columns for readability
    ws.Columns("A:G").AutoFit
    MsgBox "Scraping complete! Data saved to new worksheet.", vbInformation
End Sub

Key Improvements: #

• No more Select/ActiveCell: Uses a rowNum variable to write data directly to cells, making the code far more stable.
• User-Agent Header: Prevents Zillow from blocking your request (a common hurdle for scrapers without proper headers).
• Error Handling: Skips missing property details instead of crashing the script.
• Proper Pagination: Loops through pages until no next page exists.
• Dynamic URL: Reads the base URL from Sheet1!A1 with validation to catch empty inputs.
• Property Links: Correctly extracts the full Zillow URL for each listing.

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