如何使用VBA从Chartink选股器页面下载Excel文件并同步到本地Excel

你当前代码返回的内容和浏览器不一致，有三个核心原因：

• 你使用的User-Agent是IE6的标识，已经被绝大多数网站识别为爬虫拦截，返回的是反爬拦截页面
• Chartink页面内容是JavaScript动态渲染的，直接GET原始URL返回的是未执行JS的静态HTML模板，浏览器F12里看到的是JS执行后渲染的最终DOM
• Excel导出功能需要携带网站的CSRF令牌、合法会话Cookie才能请求成功，你当前的请求头配置不全，且没有请求正确的导出接口

这个方案直接模拟手动操作流程，不需要解析接口参数，代码如下：

Sub 下载ChartinkExcel并复制()
    Dim IE As Object
    Dim downloadPath As String
    Dim srcWB As Workbook, targetWB As Workbook
    
    ' 配置参数：下载文件默认保存路径、本地目标Excel
    downloadPath = Environ("USERPROFILE") & "\Downloads\practical-swing-trading-stock-scanner.xlsx"
    Set targetWB = ThisWorkbook ' 若要复制到其他文件，替换为Workbooks.Open("你的本地文件路径")
    
    ' 初始化IE浏览器
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True ' 调试阶段设为True可查看操作过程，稳定后可设为False后台运行
    
    ' 导航到目标页面等待加载完成
    IE.Navigate "https://chartink.com/screener/practical-swing-trading-stock-scanner"
    Do While IE.Busy Or IE.ReadyState <> 4
        DoEvents
    Loop
    Application.Wait Now + TimeValue("00:00:03") ' 预留3秒让JS完全渲染页面
    
    ' 查找并点击Excel导出按钮
    Dim btn As Object
    For Each btn In IE.Document.getElementsByTagName("button")
        If btn.innerText Like "*Excel*" Then
            btn.Click
            Exit For
        End If
    Next
    
    ' 等待下载完成，可根据自己的网速调整等待时长
    Application.Wait Now + TimeValue("00:00:10")
    
    ' 关闭IE
    IE.Quit
    Set IE = Nothing
    
    ' 复制下载文件内容到本地Excel
    Set srcWB = Workbooks.Open(downloadPath)
    ' 把下面的"目标工作表名"替换为你本地要粘贴的工作表名称
    srcWB.Sheets(1).UsedRange.Copy targetWB.Sheets("目标工作表名").Range("A1")
    srcWB.Close SaveChanges:=False
    
    MsgBox "操作完成"
End Sub

注意事项：

• 运行前先把IE的下载提示关闭，设置默认下载到系统下载文件夹，避免弹出确认框卡住流程
• 如果找不到Excel按钮，可通过F12开发者工具查看按钮的id或class属性，调整选择逻辑

需要先获取页面CSRF令牌，再请求导出接口，代码如下：

Sub 用WinHTTP下载Excel()
    Dim http As Object
    Dim html As Object
    Dim csrfToken As String
    Dim exportURL As String
    Dim fileStream As Object
    Dim downloadPath As String
    
    downloadPath = Environ("USERPROFILE") & "\Downloads\practical-swing-trading-stock-scanner.xlsx"
    Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
    Set html = CreateObject("htmlfile")
    
    ' 第一步：请求原始页面，获取CSRF令牌和会话Cookie
    http.Open "GET", "https://chartink.com/screener/practical-swing-trading-stock-scanner", False
    http.SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36"
    http.SetRequestHeader "Referer", "https://chartink.com/"
    http.Send
    
    ' 解析页面提取CSRF令牌
    html.body.innerHTML = http.ResponseText
    csrfToken = html.getElementsByName("csrf-token")(0).Content
    
    ' 第二步：请求导出接口，exportURL需要你手动抓包获取：点击Excel按钮时看F12网络面板里的export请求地址，替换下方示例
    exportURL = "https://chartink.com/screener/export/你的扫描器ID"
    http.Open "GET", exportURL, False
    http.SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36"
    http.SetRequestHeader "Referer", "https://chartink.com/screener/practical-swing-trading-stock-scanner"
    http.SetRequestHeader "X-CSRF-TOKEN", csrfToken
    http.SetRequestHeader "Cookie", http.GetResponseHeader("Set-Cookie")
    http.Send
    
    ' 保存返回的Excel二进制文件
    Set fileStream = CreateObject("ADODB.Stream")
    fileStream.Type = 1 ' 二进制类型
    fileStream.Open
    fileStream.Write http.ResponseBody
    fileStream.SaveToFile downloadPath, 2 ' 存在同名文件则覆盖
    fileStream.Close
    
    ' 复制内容到本地Excel，逻辑和方案1一致
    Dim srcWB As Workbook, targetWB As Workbook
    Set targetWB = ThisWorkbook
    Set srcWB = Workbooks.Open(downloadPath)
    srcWB.Sheets(1).UsedRange.Copy targetWB.Sheets("目标工作表名").Range("A1")
    srcWB.Close SaveChanges:=False
End Sub

注意事项：

• 导出接口的地址需要你自己用浏览器F12抓包获取，手动点击Excel按钮时，网络面板里以export开头的请求就是导出接口
• 不要短时间内频繁发起请求，避免触发网站反爬限制

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