Access VBA非调试模式导出表到Excel报找不到对象异常排查

问题现象 #

• 遍历名为_TF_Tables的表，该表存储了数据库中所有需要生成对应Excel工作簿、填充对应表数据的表名清单
• 逐行调试运行脚本时原有逻辑可正常执行：流程为创建空Excel文件，新增与目标表同名的工作表，以表名保存工作簿后，查询目标表全量数据写入新建的Excel文件
• 非调试模式直接运行脚本时触发错误，报错位置为adoConnnectionTF.Execute(strQuery)代码行，抛出异常信息：

System.Runtime.InteropServices.COMException: 'The Microsoft Access database engine could not find the object 'TF_TT'. Make sure the object exists and that you spell its name and the path name correctly. If 'TF_TT' is not a local object, check your network connection.'

• 初始实现的导出查询语句示例："SELECT * INTO [Excel 8.0;HDR=YES;DATABASE=path\TF_BLA.xlsx].[TF_BLA] FROM [TF_BLA];"
• 故障特征：脚本有时可成功生成1-2个填充了数据的Excel文件后报错，报错不固定对应某张表，无明确复现规律，定位难度大。除修复现有代码问题外，也可采用其他可实现Access指定表批量导出Excel的替代方案。

已尝试无效的排查方案 #

• 将原ADO类型记录集改为DAO记录集，错误仍然存在
• 将变量Dim声明移至循环外、精简代码并校验strQuery输出内容，错误未解决

问题根因 #

1. 循环内提前手动创建Excel实例、保存空文件的逻辑，会在非调试模式的高速执行下触发文件锁竞争：空文件保存后Excel进程未及时释放文件句柄，后续ADO执行写入时就会随机出现找不到目标对象的报错
2. 原ISAM连接参数使用Excel 8.0版本，该版本对应旧版.xls格式，和实际生成的.xlsx文件格式不匹配，会触发兼容性异常

最终可行修复方案 #

• 移除循环内提前手动创建Excel实例、保存空文件的冗余逻辑，SELECT INTO语句会自动创建目标Excel文件，不需要提前操作Excel对象生成空文件
• 将ISAM连接版本从Excel 8.0调整为Excel 12.0 Xml，适配.xlsx格式，直接通过SELECT INTO语句一步完成导出，全程不需要调用Excel COM对象，避免资源竞争问题

可直接运行的实现代码（VBA） #

Sub BatchExportTFTablesToExcel()
    Dim rs As DAO.Recordset
    Dim strExportDir As String
    Dim strTargetTable As String
    Dim strExportSQL As String
    
    ' 配置导出路径，可根据实际需求修改，注意路径末尾需保留反斜杠
    strExportDir = CurrentProject.Path & "\ExcelExport\"
    ' 自动创建不存在的导出目录
    If Dir(strExportDir, vbDirectory) = "" Then
        MkDir strExportDir
    End If
    
    ' 读取待导出表清单
    Set rs = CurrentDb.OpenRecordset("_TF_Tables", dbOpenSnapshot)
    Do While Not rs.EOF
        strTargetTable = Nz(rs!table_name, "") ' 替换为_TF_Tables表中存储表名的实际字段名
        If strTargetTable <> "" Then
            ' 拼接适配xlsx格式的导出SQL
            strExportSQL = "SELECT * INTO [Excel 12.0 Xml;HDR=YES;DATABASE=" & _
                            strExportDir & strTargetTable & ".xlsx].[" & strTargetTable & _
                            "] FROM [" & strTargetTable & "];"
            ' 执行导出，出错直接抛出提示
            CurrentDb.Execute strExportSQL, dbFailOnError
        End If
        rs.MoveNext
    Loop
    
    ' 清理资源
    rs.Close
    Set rs = Nothing
    MsgBox "指定表批量导出完成，文件存储路径：" & strExportDir
End Sub

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