Excel安全警告及VBA运行时错误'1004'求助：QueryTables.Refresh失败

问题根源 #

原代码依赖的QueryTables组件对HTTPS证书的兼容性较差，当目标网站更新SSL证书或启用更高版本TLS协议后，Excel内置的旧版SSL库无法完成证书验证，进而触发证书错误和后续的1004刷新失败。

可行解决方案 #

方案1：改用MSXML2下载文本后导入（推荐） #

放弃QueryTables，直接通过XMLHTTP对象下载数据再处理导入，避开证书和协议兼容性问题：

Sub ImportCFTCData()
    Dim http As Object
    Dim textData As String
    Dim dataRows As Variant
    Dim i As Long
    
    ' 创建HTTP对象
    Set http = CreateObject("MSXML2.XMLHTTP.6.0")
    http.Open "GET", "https://www.cftc.gov/dea/newcot/c_disagg.txt", False
    ' 模拟浏览器请求头，避免被服务器拦截
    http.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
    http.send
    
    ' 验证请求状态并处理数据
    If http.Status = 200 Then
        textData = http.responseText
        dataRows = Split(textData, vbCrLf)
        
        ' 清空目标区域并写入拆分后的数据
        ActiveSheet.Range("A1").CurrentRegion.Clear
        For i = LBound(dataRows) To UBound(dataRows)
            If Trim(dataRows(i)) <> "" Then
                ActiveSheet.Cells(i + 1, 1).Resize(1, UBound(Split(dataRows(i), ",")) + 1).Value = Split(dataRows(i), ",")
            End If
        Next i
    Else
        MsgBox "数据下载失败，状态码：" & http.Status
    End If
    
    Set http = Nothing
End Sub

• 优势：原生支持TLS1.2及以上协议，不受QueryTables的SSL限制，稳定性更高。
• 注意：Excel 2010及以上版本默认支持MSXML2.XMLHTTP.6.0。

方案2：启用Excel的TLS1.2支持（适配原代码） #

若坚持使用QueryTables，需修改注册表让Excel兼容更高版本TLS协议：

1. 按下Win+R输入regedit打开注册表编辑器。
2. 定位对应路径（32位Excel需加Wow6432Node前缀）：
   • 64位Excel：HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Common\Internet
   • 32位Excel：HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\16.0\Common\Internet
     注：16.0对应Excel 2016/2019/365，其他版本替换为对应版本号（如2013为15.0）
3. 右键Internet项，新建DWORD(32位)值，命名为TLS1.2，设置值为1。
4. 重启Excel后运行原代码。

方案3：临时绕过证书验证（应急用） #

通过API调用禁用证书检查，仅适合临时场景：

' 声明API函数
Private Declare PtrSafe Function InternetSetOption Lib "wininet.dll" _
    ByVal hInternet As LongPtr, ByVal dwOption As Long, _
    lpBuffer As Any, ByVal dwBufferLength As Long As Long

Private Const INTERNET_OPTION_SECURITY_FLAGS As Long = 31
Private Const SECURITY_FLAG_IGNORE_UNKNOWN_CA As Long = &H100
Private Const SECURITY_FLAG_IGNORE_CERT_DATE_INVALID As Long = &H200
Private Const SECURITY_FLAG_IGNORE_CERT_CN_INVALID As Long = &H400
Private Const SECURITY_FLAG_IGNORE_CERT_WRONG_USAGE As Long = &H800

Sub BypassCertCheck()
    Dim flags As Long
    flags = SECURITY_FLAG_IGNORE_UNKNOWN_CA Or _
            SECURITY_FLAG_IGNORE_CERT_DATE_INVALID Or _
            SECURITY_FLAG_IGNORE_CERT_CN_INVALID Or _
            SECURITY_FLAG_IGNORE_CERT_WRONG_USAGE
    InternetSetOption 0&, INTERNET_OPTION_SECURITY_FLAGS, flags, Len(flags)
    
    ' 运行原QueryTables代码
    With ActiveSheet.QueryTables.Add(Connection:= _
        "text;https://www.cftc.gov/dea/newcot/c_disagg.txt" _
        , Destination:=ActiveSheet.Range("A1"))
        .TextFileCommaDelimiter = True
        .BackgroundQuery = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With
End Sub

• 风险：会降低Excel网络访问的安全性，不建议长期使用。

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