如何修复从Access保存xlsx文件时出现的Error 1004错误？

一、修复运行时错误1004（无法访问文件） #

错误原因 #

当文件被当前Excel实例打开时，使用SaveAs尝试覆盖原文件会触发文件占用冲突——原文件处于打开状态，无法被新的SaveAs操作写入。

解决方案1：直接使用Save替代SaveAs #

如果只是要覆盖原文件，修改后直接调用wb.Save即可，无需使用SaveAs（SaveAs多用于另存为不同路径/格式的文件）。同时注意退出Excel实例，避免后台残留进程：

Sub ReformatXLSX(strFilePath)
  Dim appExcel As Excel.Application
  Dim wb As Excel.Workbook

  Set appExcel = New Excel.Application
  appExcel.Visible = False ' 隐藏Excel窗口，避免干扰
  ' 显式指定只读为False，确保拥有写入权限
  Set wb = appExcel.Workbooks.Open(strFilePath, ReadOnly:=False)

  ' 直接保存覆盖原文件
  wb.Save
  wb.Close SaveChanges:=False ' 已执行Save，此处无需重复保存
  appExcel.Quit ' 必须退出Excel实例，防止后台残留进程
  Set wb = Nothing
  Set appExcel = Nothing
End Sub

解决方案2：临时文件中转（适用于原文件锁定场景） #

如果原文件可能被其他进程占用，可先只读打开原文件，另存为临时文件后替换原文件：

Sub ReformatXLSX(strFilePath)
  Dim appExcel As Excel.Application
  Dim wb As Excel.Workbook
  Dim tempPath As String

  ' 生成临时文件路径
  tempPath = Left(strFilePath, InStrRev(strFilePath, ".")) & "temp.xlsx"

  Set appExcel = New Excel.Application
  appExcel.Visible = False
  ' 只读打开原文件，避免占用锁定
  Set wb = appExcel.Workbooks.Open(strFilePath, ReadOnly:=True)

  ' 另存为临时文件
  wb.SaveAs Filename:=tempPath, FileFormat:=xlOpenXMLWorkbook
  wb.Close
  appExcel.Quit
  Set wb = Nothing
  Set appExcel = Nothing

  ' 删除原文件，将临时文件重命名为原文件名
  Kill strFilePath
  Name tempPath As strFilePath
End Sub

二、无需TransferSpreadsheet的Access导入方法 #

可以使用ADODB连接直接读取XLSX文件，无需依赖Excel应用程序，效率更高且避免Excel相关的兼容性问题：

Sub ImportXLSXViaADODB(strFilePath As String, targetTableName As String)
  Dim conn As Object
  Dim strConn As String
  Dim strSQL As String

  Set conn = CreateObject("ADODB.Connection")

  ' 构建OLEDB连接字符串（适配XLSX格式）
  strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & strFilePath & ";" & _
            "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"

  conn.Open strConn

  ' 将Excel工作表数据插入Access目标表（需确保表结构匹配）
  ' 此处假设工作表名为Sheet1，可根据实际修改为你的工作表名称
  strSQL = "INSERT INTO " & targetTableName & " SELECT * FROM [Sheet1$]"
  conn.Execute strSQL

  conn.Close
  Set conn = Nothing
End Sub

参数说明 #

• HDR=YES：表示Excel第一行是表头，会作为字段名匹配；如果无表头，改为HDR=NO。
• IMEX=1：强制以读取模式处理混合数据类型的列，避免部分数据丢失。
• 需确保Access目标表的字段数量、数据类型与Excel工作表的列一致。

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