VBA运行时错误1004 ODBC刷新连接字符串报错问题求助

问题根因 #

• 代码遍历当前工作簿所有连接时未判断连接类型：wb.Connections集合包含ODBC连接、OLEDB连接、Power Query连接等多种类型，非ODBC类型的连接不存在ODBCConnection属性，访问该属性就会触发“应用程序定义或对象定义错误”
• 额外可能的触发原因：工作表1中DSN/Description/Database对应的命名范围不存在、单元格拼写错误或内容为空，拼接的连接字符串不符合ODBC规范也会触发报错

修正方案 #

优化后代码如下：

Sub updateCnnStr()
    '定义工作簿、连接、参数变量
    Dim wb As Workbook
    Dim odbc As WorkbookConnection
    Dim strDSN As String, strDesc As String, strDB As String
    
    Set wb = ThisWorkbook
    
    '预先读取参数并捕获命名范围不存在的错误
    On Error Resume Next
    strDSN = wb.Worksheets(1).Range("DSN").Value
    strDesc = wb.Worksheets(1).Range("Description").Value
    strDB = wb.Worksheets(1).Range("Database").Value
    On Error GoTo 0
    
    '参数非法直接退出，避免生成无效连接字符串
    If strDSN = "" Or strDB = "" Then
        MsgBox "DSN或Database参数为空，请检查工作表配置", vbCritical
        Exit Sub
    End If
    
    '遍历连接，仅处理ODBC类型连接
    For Each odbc In wb.Connections
        If odbc.Type = xlConnectionTypeODBC Then
            With odbc.ODBCConnection
                .Connection = _
                "ODBC;DSN=" & strDSN & ";Description=" & strDesc & ";UID=lopezro2;APP=Microsoft Office 2016;WSID=US-CCR1-L08;DATABASE=" & strDB
            End With
        End If
    Next
End Sub

额外校验项 #

• 确认工作表1中确实存在DSN、Description、Database三个命名范围，若未设置命名范围，直接替换为实际单元格坐标即可（例如Range("A1")）
• 确认当前运行宏的文件中确实存在至少1个ODBC类型的工作簿连接

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