Access中TransferSpreadsheet导出触发运行时错误3434求助

问题根源 #

1. 同一天多次导出时，生成的文件名重复（仅包含日期，无时分秒），覆盖已有Excel文件时，原文件中存在的ExceptionRNS命名范围未被正确清理，Access尝试扩展该范围时失败。
2. 编辑导入Excel文件后，原命名范围的定义与Access表的当前数据结构不匹配，导致导出时无法扩展范围。

具体解决办法 #

方法1：修改导出文件名，加入时分秒确保唯一性 #

修改导出文件名格式，让每次导出的文件名都不重复，彻底避免覆盖旧文件的问题：

Private Sub CmdExportRNS_Click()

    On Error GoTo HandleError

    Dim fullPath As String
    Dim exportFileName As String

    fullPath = CurrentProject.path
    '加入时分秒，确保文件名唯一
    exportFileName = fullPath & "\" & "myfile" & Format(Now(), " mm dd yyyy hh mm ss") & ".xlsx"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ExceptionRNS", _
      exportFileName, True
    MsgBox "File was Exported Successfully"

HandleExit:
   Exit Sub

HandleError:
   MsgBox Err.Description & " " & Err.Number
   Resume HandleExit
End Sub

方法2：导出前删除已存在的目标文件 #

如果需要保留仅含日期的文件名，在导出前检查并删除已存在的文件，避免覆盖时的命名范围冲突：

Private Sub CmdExportRNS_Click()

    On Error GoTo HandleError

    Dim fullPath As String
    Dim exportFileName As String

    fullPath = CurrentProject.path
    exportFileName = fullPath & "\" & "myfile" & Format(Now(), " mm dd yyyy") & ".xlsx"

    '如果文件存在则删除
    If Dir(exportFileName) <> "" Then
        Kill exportFileName
    End If

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ExceptionRNS", _
      exportFileName, True
    MsgBox "File was Exported Successfully"

HandleExit:
   Exit Sub

HandleError:
   MsgBox Err.Description & " " & Err.Number
   Resume HandleExit
End Sub

方法3：指定导出到工作表，绕过命名范围 #

修改TransferSpreadsheet参数，直接指定导出到工作表，不依赖自动创建的命名范围：

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ExceptionRNS", _
  exportFileName, True, "ExceptionRNS_Data"

最后一个参数"ExceptionRNS_Data"是指定的工作表名称，Access会直接将数据导出到该工作表，不会创建同名命名范围，从根源避免相关错误。

额外注意事项 #

• 导入操作中，DoCmd.SetWarnings False会关闭所有警告，建议将恢复警告的代码移到HandleExit中，确保无论是否异常都能执行：

Private Sub CmdRNSUpload_Click()
    On Error GoTo HandleError
    
    Dim file As Variant
    Dim query As String
    Dim tableNEW As String
    Dim tableDD As String
       
    DoCmd.SetWarnings False
     
    file = Forms("SelfServe").Controls("fileNameRNS").Value
    DoCmd.RunSQL "DELETE * FROM ExceptionRNS"
   
    If file = "" Then
        Forms("SelfServe").Controls("fileNameRNS").Value = ""
        MsgBox "No file selected"
        GoTo HandleExit
    End If
     
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
                             "ExceptionRNS", file, True

    Forms("SelfServe").Controls("fileNameRNS").Value = ""
    
    MsgBox "Exception List Upload it successfully"

HandleExit:
    DoCmd.SetWarnings True '移到此处，确保无论是否异常都恢复警告
    Exit Sub

HandleError:
    MsgBox Err.Description & "Something went wrong, file failed to upload"
    Resume HandleExit
End Sub

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