优化VBA代码实现IE自动下载NSE期权链CSV文件无需手动保存

原来的代码仅触发了下载按钮的点击动作，未处理IE弹出的下载确认窗口，下面给出两种可行的优化方案：

方案1：调用系统API直接下载（更稳定，无IE弹窗） #

该方案无需操作IE下载弹窗，直接获取下载链接调用系统API保存到指定路径：

' 先在模块顶部声明API函数，64位Office需替换为下文适配版本
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Sub Get_Option_Chain_Data_Auto()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim savePath As String
    ' 自定义保存路径，可自行修改
    savePath = "D:\option_chain.csv"
    
    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim downloadBtn As MSHTML.IHTMLElement
    
    IE.Visible = False
    IE.navigate "https://www.nseindia.com/option-chain"
    
    ' 等待页面完全加载，比单纯判断readyState更可靠
    Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    Application.Wait Now + TimeValue("00:00:03") ' 预留3秒等待动态数据加载，可根据网络情况调整
    
    Set HTMLDoc = IE.document
    Set downloadBtn = HTMLDoc.getElementById("downloadOCTable")
    
    ' 获取下载按钮的实际下载链接
    Dim downloadUrl As String
    downloadUrl = downloadBtn.href
    
    ' 调用API直接下载保存到指定路径
    Dim downloadRes As Long
    downloadRes = URLDownloadToFile(0, downloadUrl, savePath, 0, 0)
    
    If downloadRes = 0 Then
        MsgBox "文件下载成功，保存路径：" & savePath
    Else
        MsgBox "文件下载失败，请检查网络或链接有效性"
    End If
    
    IE.Quit
    Set IE = Nothing
    Set HTMLDoc = Nothing
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

方案2：保留IE操作，自动处理下载弹窗 #

如果必须使用IE的下载流程，可以用SendKeys模拟按键操作处理弹窗，该方案需要IE窗口可见，运行时不要操作键鼠：

Sub Get_Option_Chain_Data_IE_AutoSave()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim downloadBtn As MSHTML.IHTMLElement
    
    IE.Visible = True ' 必须设为可见SendKeys才能生效
    IE.navigate "https://www.nseindia.com/option-chain"
    
    Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    Application.Wait Now + TimeValue("00:00:03")
    
    Set HTMLDoc = IE.document
    Set downloadBtn = HTMLDoc.getElementById("downloadOCTable")
    downloadBtn.Click
    
    ' 等待下载弹窗弹出
    Application.Wait Now + TimeValue("00:00:02")
    ' 模拟Alt+S 触发保存，部分IE版本可直接按S保存，可根据实际情况调整按键
    Application.SendKeys "%{S}", True
    ' 等待保存完成
    Application.Wait Now + TimeValue("00:00:02")
    
    IE.Quit
    Set IE = Nothing
    Set HTMLDoc = Nothing
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

注意事项 #

• 运行前需要先在VBA编辑器的「工具」-「引用」菜单中勾选「Microsoft Internet Controls」和「Microsoft HTML Object Library」
• 64位Office使用方案1时，需要将模块顶部的API声明替换为适配版本：
  Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As LongPtr) As Long
• NSE网站有动态数据加载延迟，可根据自身网络情况调整代码中的等待时长
• 方案2的SendKeys对运行环境依赖较高，优先使用稳定性更强的方案1
• 如果出现下载失败，可先手动访问目标网站确认页面加载正常、下载按钮可正常点击

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