Hey there! Let's expand your existing VBA script to automatically pull product name, description, price, and image URL from an Amazon product page and write them into an Excel spreadsheet. Here's the complete, working version of the code:

Step 1: Enable Required References #

First, make sure your VBA project has these references enabled (go to Tools > References in the VBA editor):

• Microsoft Internet Controls
• Microsoft HTML Object Library

Complete VBA Code #

Sub ExtractAmazonProductDetails()
    Dim ieObj As InternetExplorer
    Dim ht As HTMLDocument
    Dim productName As String
    Dim productDesc As String
    Dim productPrice As String
    Dim imageURL As String
    Dim descElements As IHTMLElementCollection
    Dim descElement As HTMLDivElement
    Dim imgElements As IHTMLElementCollection
    Dim imgElement As HTMLImg
    
    ' Replace this URL with your target product page
    Dim website As String
    website = "https://www.amazon.com/resistencia-Avalon-cartas-empaque-original/dp/B009SAAV0C?pf_rd_r=WWESR922Z214Y10K3PHH&pf_rd_p=4dd821c0-e689-433a-a035-5e03461484eb&pd_rd_r=305599f9-5f3f-41c6-9a13-8daefd8d998c&pd_rd_w=qWHso&pd_rd_wg=BNzqC&ref_=pd_gw_unk"
    
    ' Initialize IE object
    Set ieObj = New InternetExplorer
    ieObj.Visible = True ' Set to False to run browser in background
    ieObj.navigate website
    
    ' Wait for page to fully load
    Do Until ieObj.readyState = READYSTATE_COMPLETE
        DoEvents
    Loop
    Set ht = ieObj.document
    
    ' Extract Product Name (using the ID you provided)
    On Error Resume Next ' Safeguard if element is missing
    productName = Trim(ht.getElementById("productTitle").innerText)
    On Error GoTo 0
    
    ' Extract Product Description (combine all a-list-item sections)
    productDesc = ""
    Set descElements = ht.getElementsByClassName("a-list-item")
    For Each descElement In descElements
        If Trim(descElement.innerText) <> "" Then
            productDesc = productDesc & Trim(descElement.innerText) & vbNewLine
        End If
    Next descElement
    productDesc = Trim(productDesc) ' Clean up extra spaces/newlines
    
    ' Extract Product Price
    On Error Resume Next
    productPrice = Trim(ht.getElementById("priceblock_ourprice").innerText)
    On Error GoTo 0
    
    ' Extract Image URL (matching the alt text you specified)
    Set imgElements = ht.getElementsByTagName("img")
    For Each imgElement In imgElements
        If imgElement.alt = "The Resistance: Avalon Social Deduction Game" Then
            imageURL = imgElement.src
            Exit For ' Stop searching once we find the correct image
        End If
    Next imgElement
    
    ' Write data to Excel (adjust sheet name as needed)
    With ThisWorkbook.Sheets("Sheet1")
        .Range("A1").Value = "Product Name"
        .Range("B1").Value = productName
        .Range("A2").Value = "Product Description"
        .Range("B2").Value = productDesc
        .Range("A3").Value = "Price"
        .Range("B3").Value = productPrice
        .Range("A4").Value = "Image URL"
        .Range("B4").Value = imageURL
    End With
    
    ' Clean up resources
    ieObj.Quit
    Set ieObj = Nothing
    Set ht = Nothing
    Set descElements = Nothing
    Set imgElements = Nothing
    
    MsgBox "Product details extracted successfully!", vbInformation
End Sub

Key Breakdowns #

• Product Name: Targets the element by its unique ID productTitle and trims extra whitespace for cleanliness.
• Product Description: Loops through all elements with class a-list-item, concatenating their text to capture the full description.
• Price: Grabs the price directly via the priceblock_ourprice ID you provided.
• Image URL: Scans all <img> tags and matches the specified alt text to pull the correct image source link.

Quick Notes #

• Amazon’s page structure can change over time—if the script stops working, use your browser’s developer tools to re-inspect element IDs/classes.
• For faster runs, set ieObj.Visible = False to hide the browser window.
• Add more robust error handling if you need to account for missing elements or broken URLs.

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