VBA网页抓取数据Excel自动更新求助：HTMLRow的.refresh方法失效

Got it, let's work through this auto-refresh problem for your bookmaker odds scraping code. The .RefreshPeriod and .Refresh methods you tried probably didn't work because they're meant for specific Excel web query objects—not the raw HTML row variables you're using to pull odds data. Here are a few practical, tested solutions to get your Excel data syncing automatically with the webpage updates:

Since you already have working code to extract odds, the simplest fix is to package that logic into a reusable sub, then set up a timer to run it on your desired interval.

• First, create a dedicated scraping sub (replace the placeholder code with your existing scraping logic):

Sub RefreshOddsData()
    ' Initialize browser object
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False ' Keep hidden for background runs; set to True for debugging
    IE.Navigate "YourBookmakersURLHere"
    
    ' Wait for page to fully load
    Do While IE.Busy Or IE.ReadyState <> 4
        DoEvents
    Loop
    
    ' Your existing code to extract odds from HTML rows goes here
    ' Example: Pull data from rows with a specific class
    Dim targetRows As Object
    Set targetRows = IE.document.getElementsByClassName("odds-row")
    Dim rowIndex As Integer
    rowIndex = 1
    
    For Each row In targetRows
        Sheet1.Cells(rowIndex, 1).Value = row.Cells(0).innerText ' Adjust cell references as needed
        ' Add lines to extract other odds values here
        rowIndex = rowIndex + 1
    Next row
    
    ' Clean up
    IE.Quit
    Set IE = Nothing
End Sub

• Then, set up a timer using Application.OnTime to trigger the refresh:

Sub EnableAutoRefresh()
    ' Set refresh interval to 15 minutes (adjust to your needs)
    Dim nextRunTime As Date
    nextRunTime = Now + TimeValue("00:15:00")
    
    ' Schedule the next refresh
    Application.OnTime nextRunTime, "RefreshOddsData"
    
    ' Optional: Confirm the schedule is set
    MsgBox "Auto-refresh enabled. Data will update every 15 minutes.", vbInformation
End Sub

' Use this to stop the auto-refresh if needed
Sub DisableAutoRefresh()
    On Error Resume Next ' Prevent error if no refresh is scheduled
    Application.OnTime Now + TimeValue("00:15:00"), "RefreshOddsData", , False
    On Error GoTo 0
    MsgBox "Auto-refresh disabled.", vbInformation
End Sub

If you originally imported the web data using Excel's native web query tool, you might have been targeting the wrong object. QueryTables have proper .Refresh and .RefreshPeriod properties that work reliably.

Sub ConfigureQueryTableAutoRefresh()
    Dim qt As QueryTable
    ' Assume your data is in Sheet1—adjust the sheet and QueryTable index as needed
    Set qt = Sheet1.QueryTables(1)
    
    ' Set refresh interval (in minutes)
    qt.RefreshPeriod = 15
    ' Allow background refresh so Excel stays usable
    qt.BackgroundQuery = True
    ' Trigger an immediate refresh
    qt.Refresh
End Sub

Note: This only works if your data was imported via a QueryTable, not if you're scraping raw HTML with IE or custom HTTP requests.

XMLHTTP is lighter than Internet Explorer—it fetches raw HTML without launching a browser, making it ideal for frequent refreshes.

Sub RefreshOddsWithXMLHTTP()
    Dim xmlHttp As Object
    Dim htmlDoc As Object
    Dim oddsRows As Object
    Dim rowIndex As Integer
    
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    xmlHttp.Open "GET", "YourBookmakersURLHere", False
    xmlHttp.send
    
    ' Load response into an HTML document object
    Set htmlDoc = CreateObject("HTMLFile")
    htmlDoc.body.innerHTML = xmlHttp.responseText
    
    ' Extract odds data (match this to your existing scraping logic)
    Set oddsRows = htmlDoc.getElementsByClassName("odds-row")
    rowIndex = 1
    
    For Each row In oddsRows
        Sheet1.Cells(rowIndex, 1).Value = row.Cells(0).innerText
        ' Add lines to pull other odds values
        rowIndex = rowIndex + 1
    Next row
    
    ' Clean up
    Set xmlHttp = Nothing
    Set htmlDoc = Nothing
End Sub

Pair this with the Application.OnTime timer from Solution 1 to schedule automatic updates.

• If the website uses JavaScript to load odds dynamically, XMLHTTP won't capture that content. In this case, use Selenium Basic (a free VBA-compatible browser tool) to wait for JS-rendered data to load.
• Ensure your VBA project has the right references enabled: For IE, check "Microsoft Internet Controls"; for XMLHTTP, check "Microsoft XML, v6.0".
• Add error handling to your scraping sub to handle website downtime or changes to the HTML structure (e.g., On Error Resume Next with checks for missing elements).

内容的提问来源于stack exchange，提问作者Issy the kid