Python网页爬虫无法向Excel返回值，请求调试解决

核心问题诊断 #

• VBA阻塞问题：StdOut.ReadAll()会等待Python脚本完全执行结束才返回结果，但你的Python脚本是无限循环（while True），永远不会终止，导致VBA一直卡住，无法获取实时输出。
• 输出干扰问题：Python脚本开头的提示文本（如"Waiting for price element..."）会混入输出，导致VBA的IsNumeric判断失败，无法识别有效价格。
• 路径安全问题：如果Python可执行文件或脚本路径包含空格，未加引号会导致Shell执行失败，无任何输出。

修复步骤 #

1. 修改Python脚本（仅输出价格数值） #

移除多余的提示打印，确保stdout只输出纯价格数值，同时将错误信息输出到stderr避免干扰：

import time
import logging
import sys
from selenium import webdriver
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC

# Suppress all unnecessary logs
logging.basicConfig(level=logging.CRITICAL)
logging.getLogger("selenium").setLevel(logging.CRITICAL)

chromedriver_path = 'C:/GoldScraper/chromedriver.exe'

options = Options()
options.add_argument("--headless")
options.add_argument("--no-sandbox")
options.add_argument("--disable-dev-shm-usage")
options.add_argument("--disable-gpu")
options.add_argument("start-maximized")
options.add_argument("--log-level=3")
options.add_argument("--disable-software-rasterizer")
options.add_argument("--disable-extensions")
options.add_argument("--disable-logging")

service = Service(chromedriver_path)
driver = webdriver.Chrome(service=service, options=options)

driver.get('https://www.cnbc.com/quotes/XAU=')

try:
    gold_price_element = WebDriverWait(driver, 10).until(
        EC.visibility_of_element_located((By.CLASS_NAME, "QuoteStrip-lastPrice"))
    )

    while True:
        gold_price = round(float(gold_price_element.text.replace(",", "")), 2)
        # 仅输出纯价格数值
        print(gold_price)
        sys.stdout.flush()
        time.sleep(1)

except KeyboardInterrupt:
    print("\nScript stopped by user.", file=sys.stderr)

except Exception as e:
    print(f"Error extracting gold price: {e}", file=sys.stderr)
    sys.stderr.flush()

finally:
    driver.quit()
    print("Driver closed.", file=sys.stderr)

2. 修改VBA代码（实时逐行读取输出） #

改用逐行读取stdout，循环更新Excel单元格，同时处理路径空格和错误信息：

Sub StartScraping()
    Dim objShell As Object
    Dim pythonProcess As Object
    Dim pythonExePath As String
    Dim pythonScriptPath As String
    Dim outputLine As String
    
    ' 路径含空格时必须用双引号包裹
    pythonExePath = """C:\python313\python.exe"""
    pythonScriptPath = """C:\GoldScraper\goldscraper.py"""
    
    Set objShell = CreateObject("WScript.Shell")
    ' 启动Python进程并获取进程对象
    Set pythonProcess = objShell.Exec(pythonExePath & " " & pythonScriptPath)
    
    ' 循环读取输出直到进程结束
    Do While pythonProcess.Status = 0
        ' 读取Python每秒输出的一行内容
        outputLine = pythonProcess.StdOut.ReadLine()
        
        ' 仅处理数值内容，更新指定单元格
        If IsNumeric(outputLine) Then
            ThisWorkbook.Names("Live_AU").RefersToRange.Value = CDbl(outputLine)
            ' 强制刷新Excel界面
            DoEvents
        End If
        
        ' 检查并弹出Python的错误信息
        If Not pythonProcess.StdErr.AtEndOfStream Then
            MsgBox "Python Error: " & pythonProcess.StdErr.ReadLine(), vbCritical
            Exit Do
        End If
    Loop
    
    ' 清理对象
    Set pythonProcess = Nothing
    Set objShell = Nothing
End Sub

Sub StopScraping()
    ' 终止Python和ChromeDriver进程
    Dim objShell As Object
    Set objShell = CreateObject("WScript.Shell")
    objShell.Run "taskkill /F /IM chromedriver.exe", 0, True
    objShell.Run "taskkill /F /IM python.exe", 0, True
    Set objShell = Nothing
    MsgBox "Scraping stopped."
End Sub

额外注意事项 #

• 确保ChromeDriver版本与本地Chrome浏览器版本完全匹配，否则Selenium启动会失败。
• 运行VBA宏前需在Excel中启用宏信任设置。
• 若遇权限问题，右键以管理员身份运行Excel。

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