关于从Amazon页面<a data-params>提取数据及Excel VBA代码优化的技术问询

Hey there! Let's get your VBA script working correctly to pull those ASINs from Amazon's product pages and write them to Excel column B. First, let's go over the key issues in your original code, then share the revised version with clear explanations.

Key Issues in the Original Code #

• Incorrect object reference: You tried using Right$(myLink, 9) directly on the myLink HTML element object, but you need to access its href property instead (since myLink isn't a raw string).
• Invalid quotation marks: The code uses Chinese-style quotes (“a”) in getElementsByTagName — these will throw a compile error; stick to standard English double quotes ("a").
• Redundant document setup: You don't need to reassign html.body.innerHTML = result because html already points to the fully loaded page's document.
• Unreliable ASIN extraction: Checking just the right 9 characters isn't precise. We need to specifically target the 10-character ASIN code that follows /dp/ in the link's href value.

Corrected VBA Code #

Sub GetAmazonASINs()
    Dim internet As Object
    Dim htmlDoc As Object
    Dim allLinks As Object
    Dim singleLink As Object
    Dim targetURL As String
    Dim lastRow As Long
    Dim asinStartPos As Integer
    Dim asin As String
    
    ' Initialize Internet Explorer object
    Set internet = CreateObject("InternetExplorer.Application")
    internet.Visible = True ' Set to False if you don't want the IE window to show
    
    ' Get the last row with data in column A
    lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each URL in column A (starting from row 2, assuming row 1 is a header)
    For i = 2 To lastRow
        targetURL = Sheet1.Cells(i, 1).Value
        
        ' Navigate to the target Amazon page
        internet.navigate targetURL
        
        ' Wait for the page to fully load (covers dynamic content delays)
        Do While internet.ReadyState <> 4 Or internet.Busy
            DoEvents
        Loop
        
        ' Grab the loaded page's document object
        Set htmlDoc = internet.document
        
        ' Collect all <a> tags on the page
        Set allLinks = htmlDoc.getElementsByTagName("a")
        
        ' Loop through links to find those with ref=dp_cerb_1 in their href
        For Each singleLink In allLinks
            If InStr(singleLink.href, "ref=dp_cerb_1") > 0 Then
                ' Extract ASIN: find "/dp/" and take the next 10 characters
                asinStartPos = InStr(singleLink.href, "/dp/") + 4
                asin = Mid(singleLink.href, asinStartPos, 10)
                
                ' Write the ASIN to column B of the current row
                Sheet1.Cells(i, 2).Value = asin
                Exit For ' Stop looping once we find the target link (assuming one per page)
            End If
        Next singleLink
    Next i
    
    ' Clean up resources
    internet.Quit
    Set internet = Nothing
    Set htmlDoc = Nothing
    Set allLinks = Nothing
    Set singleLink = Nothing
    
    MsgBox "ASIN extraction completed successfully!", vbInformation
End Sub

How to Use This Code #

1. Open your Excel workbook with Amazon links in column A.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook in the Project Explorer > Insert > Module.
4. Paste the corrected code into the module.
5. Press F5 to run the macro, or assign it to a button for easier access later.

Quick Notes #

• Page Loading: The script waits for both ReadyState = 4 and Busy = False to ensure Amazon's dynamic content finishes loading before scraping.
• ASIN Length: This assumes standard 10-character Amazon ASINs. If you encounter exceptions, adjust the Mid function's character count.
• Stealth Mode: Set internet.Visible = False if you don't want the IE window to pop up while the script runs in the background.

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