如何修复VBA运行时错误（Automation Error）？

各位好，我在运行以下VBA代码时遇到了Automation Error运行时错误，恳请各位帮忙解决该问题。

错误截图：

运行的VBA代码：

Public Sub DownloadFile()

Dim objIE As InternetExplorer, currPage As HTMLDocument, url As String
Dim elements

url = "https://www.nseindia.com/companies-listing/corporate-filings-insider-trading"

Set objIE = New InternetExplorer
objIE.navigate url

Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

Set currPage = objIE.document
objIE.Visible = True

'objIE.document.getElementById("CFinsidertrading-download").Click
Set elements = objIE.document.getElementsByClassName("dayslisting")

For Each element In elements  'loop through all <a></a> elements...
    Set Links = element.getElementsByTagName("a")
    For Each link In Links
        If link.innerHTML = "3M" Then
            link.Click
            Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
            GoTo lbl3m
        End If
    Next link

Next element
lbl3m:
objIE.document.getElementById("CFinsidertrading-download").Click
Application.Wait Now + TimeSerial(0, 0, 40) 'alter to give enough time for window

Application.SendKeys "%{S}"
'Application.Wait Now + TimeSerial(0, 0, 20) 'alter to give enough time for window
'MsgBox "File downloaded"

End Sub

排查与解决步骤 #

• 检查引用库：
  打开VBA编辑器，点击「工具」→「引用」，确认已勾选「Microsoft Internet Controls」和「Microsoft HTML Object Library」。若找不到，通过「浏览」定位系统目录下的ieframe.dll和mshtml.dll添加。

• 优化页面等待逻辑：
  原有的就绪判断可能存在遗漏，替换为更可靠的等待：

  ' 替换原有等待代码
  Do Until objIE.readyState = 4 And Not objIE.Busy
      DoEvents
  Loop
  ' 点击"3M"链接后额外等待DOM加载
  Application.Wait Now + TimeSerial(0, 0, 3)
  

• 替换不可靠的SendKeys：
  SendKeys依赖窗口焦点易出错，改用API直接下载：

  ' 模块顶部声明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 LongPtr, ByVal lpfnCB As LongPtr) As LongPtr
  
  ' 调用示例（需先获取真实下载链接）
  Dim downloadPath As String
  downloadPath = "C:\YourSavePath\insider_trading.csv"
  URLDownloadToFile 0, "真实下载链接地址", downloadPath, 0, 0
  

• 添加错误捕获与对象释放：
  在代码中加入错误定位逻辑，避免内存泄漏：

  On Error GoTo ErrorHandler
  ' 原有代码...
  
  Exit Sub
  ErrorHandler:
      MsgBox "错误编号: " & Err.Number & vbCrLf & "错误描述: " & Err.Description
      Set objIE = Nothing
      Set currPage = Nothing
  

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