求助：Excel VBA实现从指定URL批量抓取数据至表格

Hey there! As someone new to Excel VBA web scraping, I get that putting all the pieces together can feel overwhelming—let's walk through building your macro step by step, with clear examples and explanations tailored to your needs.

Before we start writing code, you need to enable two key libraries so VBA can interact with web pages and HTML:

1. Open the VBA editor (press Alt + F11 in Excel)
2. Go to Tools > References
3. Check the boxes for:
   • Microsoft Internet Controls (for using Internet Explorer)
   • Microsoft HTML Object Library (for parsing HTML)

Looking at your initial code, I notice you're linking to preViewMP.do, but your base search URL is preSearchMP.do. Make sure you're using the correct endpoint for searching. Also, you'll need to confirm the exact parameter name the website expects for your search value (e.g., if the site uses mpNumber= as the parameter key, your URL should include that).

For example, if your search value is the MP number, your URL might look like this:

targetURL = "http://XXXX-XXXXX.eu.airbus.XXXX:XXXXX/XXXX/consultation/preSearchMP.do?" & _
            "clearBackList=true&CMH_NO_STORING_fromMenu=true&mpNumber=" & searchValue

To find the right parameter name: manually perform a search in your browser, then check the URL bar or use the Network tab in developer tools (F12) to see what parameters are sent.

Internet Explorer is great for beginners because it's visible—you can see exactly what the macro is doing, which makes debugging easier. Here's a complete, commented macro that loops through your data, searches, and returns results:

Sub ScrapeMPData()
    Dim ie As InternetExplorer
    Dim htmlDoc As HTMLDocument
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim searchValue As String
    Dim targetURL As String
    
    ' Set the worksheet with your data (change "Feuil1" if needed)
    Set ws = ThisWorkbook.Sheets("Feuil1")
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Initialize Internet Explorer
    Set ie = New InternetExplorer
    ie.Visible = True ' Keep this as True to see the browser; set to False for hidden runs
    
    ' Loop through each row in column A
    For i = 1 To lastRow
        searchValue = ws.Cells(i, "A").Value
        If searchValue <> "" Then ' Skip empty cells
            
            ' Build the search URL (replace "mpNumber" with your actual parameter)
            targetURL = "http://XXXX-XXXXX.eu.airbus.XXXX:XXXXX/XXXX/consultation/preSearchMP.do?" & _
                        "clearBackList=true&CMH_NO_STORING_fromMenu=true&mpNumber=" & searchValue
            
            ' Navigate to the search page
            ie.Navigate targetURL
            
            ' Wait for the page to fully load (critical!)
            Do While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE
                DoEvents ' Let Excel handle other tasks while waiting
            Loop
            
            ' Get the page's HTML content
            Set htmlDoc = ie.Document
            
            ' --------------------------
            ' This part depends on your target data's structure
            ' Example: If your result is in a <div> with class "mp-result-text"
            Dim resultElement As Object
            Set resultElement = htmlDoc.querySelector(".mp-result-text") ' Uses CSS selector
            ' Alternative: Get by ID: htmlDoc.getElementById("result-id")
            ' Alternative: Get by tag name: htmlDoc.getElementsByTagName("p")(0)
            
            ' Write the result to column B of the same row
            If Not resultElement Is Nothing Then
                ws.Cells(i, "B").Value = resultElement.innerText
            Else
                ws.Cells(i, "B").Value = "No result found"
            End If
            ' --------------------------
            
        End If
    Next i
    
    ' Clean up resources
    ie.Quit
    Set ie = Nothing
    Set htmlDoc = Nothing
    Set ws = Nothing
    
    MsgBox "Scraping finished! Check column B for results.", vbInformation
End Sub

If you don't need to see the browser, XMLHTTP is faster and runs in the background. Note: This only works if the page loads all data in the initial HTML (no dynamic JavaScript loading). Here's how to adapt the macro:

Sub ScrapeWithXMLHTTP()
    Dim xhr As XMLHTTP60
    Dim htmlDoc As HTMLDocument
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim searchValue As String
    Dim targetURL As String
    
    Set ws = ThisWorkbook.Sheets("Feuil1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Initialize XMLHTTP object
    Set xhr = New XMLHTTP60
    
    For i = 1 To lastRow
        searchValue = ws.Cells(i, "A").Value
        If searchValue <> "" Then
            
            targetURL = "http://XXXX-XXXXX.eu.airbus.XXXX:XXXXX/XXXX/consultation/preSearchMP.do?" & _
                        "clearBackList=true&CMH_NO_STORING_fromMenu=true&mpNumber=" & searchValue
            
            ' Send a GET request to the URL
            xhr.Open "GET", targetURL, False
            xhr.send
            
            ' Check if the request was successful (status 200 = OK)
            If xhr.Status = 200 Then
                Set htmlDoc = New HTMLDocument
                htmlDoc.body.innerHTML = xhr.responseText
                
                ' Extract data (same as the IE example)
                Dim resultElement As Object
                Set resultElement = htmlDoc.querySelector(".mp-result-text")
                
                If Not resultElement Is Nothing Then
                    ws.Cells(i, "B").Value = resultElement.innerText
                Else
                    ws.Cells(i, "B").Value = "No result found"
                End If
            Else
                ws.Cells(i, "B").Value = "Error: " & xhr.Status & " - Request failed"
            End If
        End If
    Next i
    
    ' Clean up
    Set xhr = Nothing
    Set htmlDoc = Nothing
    Set ws = Nothing
    
    MsgBox "Scraping completed!", vbInformation
End Sub

• Debug Like a Pro: Use breakpoints (press F9 on a code line) to pause the macro and check values (e.g., targetURL to make sure it's correctly formatted).
• Inspect the Page: Use your browser's developer tools (F12) to find the exact HTML element containing your target data. Right-click the data and select "Inspect" to see its class, ID, or tag name.
• Handle Dynamic Content: If the page loads data after the initial load (e.g., with a spinner), stick with Internet Explorer—XMLHTTP can't capture dynamically loaded content. For advanced dynamic pages, you could look into Selenium, but that's a bigger learning curve.
• Add Error Handling: To prevent crashes, add basic error handling. For example, wrap the navigation/extraction part in On Error Resume Next or a structured error block.

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