如何用VBA Selenium获取亚马逊商品价格并实现Excel写入与每日调度

一、VBA优化方案 #

1. 修复后的简化代码 #

原代码存在多次覆盖C列值、无效变量、错误处理粗糙的问题，以下是优化后的版本，可正确抓取亚马逊价格并区分苹果/亚马逊站点：

Sub AmazonApple_Scraper()
    Dim selenium As New SeleniumWrapper.WebDriver
    Dim intRowPosition As Integer
    Dim currentUrl As String
    Dim priceText As String
    
    ' 初始化浏览器
    selenium.AddArgument "start-maximized"
    selenium.Start "chrome"
    selenium.setTimeout "120000"
    selenium.setImplicitWait "5000"
    
    intRowPosition = 3 ' 从第3行开始读取URL
    
    ' 遍历A列所有非空URL
    Do While Sheet1.Range("A" & intRowPosition).Value <> vbNullString
        currentUrl = Sheet1.Range("A" & intRowPosition).Value
        
        ' 打开新标签页并访问URL
        selenium.SendKeys selenium.keys.Control & "t"
        selenium.Open currentUrl
        
        ' 抓取标题（优先用页面标题，亚马逊也可直接抓productTitle元素）
        On Error Resume Next
        Sheet1.Range("B" & intRowPosition).Value = selenium.FindElementById("productTitle").Text
        If Err.Number <> 0 Then
            Sheet1.Range("B" & intRowPosition).Value = selenium.getTitle
        End If
        On Error GoTo 0
        
        ' 区分站点抓取价格
        If InStr(currentUrl, "amazon") > 0 Then
            ' 抓取亚马逊价格：拼接符号、整数、小数部分
            On Error Resume Next
            Dim priceSymbol As String, priceWhole As String, priceFraction As String
            priceSymbol = selenium.FindElementByCssSelector(".a-price-symbol").Text
            priceWhole = selenium.FindElementByCssSelector(".a-price-whole").Text
            priceFraction = selenium.FindElementByCssSelector(".a-price-fraction").Text
            priceText = priceSymbol & Replace(priceWhole, ".", "") & "." & priceFraction
            Sheet1.Range("C" & intRowPosition).Value = priceText
            On Error GoTo 0
        ElseIf InStr(currentUrl, "apple.com") > 0 Then
            ' 抓取苹果商店价格
            On Error Resume Next
            Sheet1.Range("C" & intRowPosition).Value = selenium.findElementByCssSelector(".rf-bfe-header .as-price-currentprice span").Text
            ' 兼容iPad Pro特殊价格结构
            If Sheet1.Range("C" & intRowPosition).Value = "" Then
                Sheet1.Range("C" & intRowPosition).Value = selenium.FindElementByXPath("(//div[@class='rc-prices-currentprice typography-label'])[2]/span").Text
            End If
            On Error GoTo 0
        End If
        
        intRowPosition = intRowPosition + 1
    Loop
    
    ' 关闭浏览器
    selenium.Close
    Set selenium = Nothing
End Sub

2. 每日自动运行设置 #

通过Windows任务计划实现每日自动执行：

• 打开「任务计划程序」，创建「基本任务」
• 触发条件选择「每日」，设置执行时间
• 操作选择「启动程序」，程序/脚本选择Excel的安装路径（如C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE）
• 添加参数：/e "C:\你的文件路径\你的Excel文件名.xlsm" /m "AmazonApple_Scraper"（注意路径和宏名要准确）
• 完成设置，确保Excel文件启用宏，且任务执行账户有足够权限

二、Python Selenium方案（更优选择） #

Python在爬虫生态、反爬处理、代码可维护性上更有优势，以下是实现代码：

1. 依赖安装 #

先安装所需库：

pip install selenium openpyxl webdriver-manager

2. 完整爬虫代码 #

from selenium import webdriver
from selenium.webdriver.chrome.service import Service
from webdriver_manager.chrome import ChromeDriverManager
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
import openpyxl
import time

def scrape_amazon_apple():
    # 初始化浏览器
    options = webdriver.ChromeOptions()
    options.add_argument("--start-maximized")
    options.add_argument("--disable-blink-features=AutomationControlled")
    driver = webdriver.Chrome(service=Service(ChromeDriverManager().install()), options=options)
    wait = WebDriverWait(driver, 20)
    
    # 打开Excel文件
    wb = openpyxl.load_workbook("你的Excel文件名.xlsx")
    sheet = wb.active
    row_position = 3  # 从第3行开始
    
    # 遍历URL
    while sheet[f"A{row_position}"].value is not None:
        current_url = sheet[f"A{row_position}"].value
        driver.execute_script(f"window.open('{current_url}');")
        driver.switch_to.window(driver.window_handles[-1])
        
        # 抓取标题
        try:
            if "amazon" in current_url:
                title = wait.until(EC.presence_of_element_located((By.ID, "productTitle"))).text.strip()
            else:
                title = wait.until(EC.presence_of_element_located((By.TAG_NAME, "h1"))).text.strip()
            sheet[f"B{row_position}"] = title
        except Exception as e:
            sheet[f"B{row_position}"] = "标题抓取失败"
        
        # 抓取价格
        try:
            if "amazon" in current_url:
                price_symbol = wait.until(EC.presence_of_element_located((By.CSS_SELECTOR, ".a-price-symbol"))).text
                price_whole = wait.until(EC.presence_of_element_located((By.CSS_SELECTOR, ".a-price-whole"))).text.replace(".", "")
                price_fraction = wait.until(EC.presence_of_element_located((By.CSS_SELECTOR, ".a-price-fraction"))).text
                price = f"{price_symbol}{price_whole}.{price_fraction}"
            else:
                price = wait.until(EC.presence_of_element_located((By.CSS_SELECTOR, ".rf-bfe-header .as-price-currentprice span"))).text
                if not price:
                    price = wait.until(EC.presence_of_element_located((By.XPATH, "(//div[@class='rc-prices-currentprice typography-label'])[2]/span"))).text
            sheet[f"C{row_position}"] = price
        except Exception as e:
            sheet[f"C{row_position}"] = "价格抓取失败"
        
        # 关闭当前标签页，切回第一个标签页
        driver.close()
        driver.switch_to.window(driver.window_handles[0])
        row_position += 1
    
    # 保存Excel并关闭浏览器
    wb.save("你的Excel文件名.xlsx")
    driver.quit()

if __name__ == "__main__":
    scrape_amazon_apple()

3. 每日自动运行设置 #

• 打开「任务计划程序」，创建「基本任务」
• 触发条件选择「每日」，设置执行时间
• 操作选择「启动程序」，程序/脚本选择Python的安装路径（如C:\Users\你的用户名\AppData\Local\Programs\Python\Python311\python.exe）
• 添加参数："C:\你的脚本路径\爬虫脚本名.py"
• 完成设置，确保脚本和Excel文件路径正确

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